一、資料準備
有如下資料，學生id，課程，分數
1,yuwen,43
1,shuxue,55
2,yuwen,77
2,shuxue,88
3,yuwen,98
3,shuxue,65
二、需求分析
1、創建表
create table requirement( sid int, course string, score int ) row format delimited fields terminated by ',';
2、上傳資料
load data local inpath '/usr/mydir/data/requirement.txt' into table requirement;
3、驗證資料是否正確
select * from requirement;
4、查詢資料
方法一：
select sid, max(math) as math, max(chinese) as chinese from ( select *, case course when 'shuxue' then score else 0 end as math, case course when 'yuwen' then score else 0 end as chinese from requirement) t group by sid having math > chinese;
方法二：
SELECT a.sid,a.score math,b.score chinese FROM (select sid,course,score FROM requirement where course = 'shuxue') a left join (select sid,course,score FROM requirement where course = 'yuwen') b on a.sid = b.sid where a.score >= b.score;
！注意：使用方法二進行連接查詢，運行時所消耗的時間較多！
三、結果
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/206097.html
標籤：大數據