一、数据准备

有如下数据，学生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;

！注意：使用方法二进行连接查询，运行时所消耗的时间较多！

 

三、结果