Hive经典案例：求出数学成绩大于语文成绩学生的信息

create table requirement(
    sid int,
    course string,
    score int
)
row format delimited fields terminated by ‘,‘;

load data local inpath ‘/usr/mydir/data/requirement.txt‘ into table requirement;

select * from requirement;

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;