本文共 1502 字，大约阅读时间需要 5 分钟。

题目背景是分析学生课程成绩，相关数据如下：

1、说明

use myhive;CREATE TABLE `course` (  `id` int,  `sid` int ,  `course` string,  `score` int ) ;

// 插入数据// 字段解释：id, 学号， 课程， 成绩INSERT INTO `course` VALUES (1, 1, 'yuwen', 43);INSERT INTO `course` VALUES (2, 1, 'shuxue', 55);INSERT INTO `course` VALUES (3, 2, 'yuwen', 77);INSERT INTO `course` VALUES (4, 2, 'shuxue', 88);INSERT INTO `course` VALUES (5, 3, 'yuwen', 98);INSERT INTO `course` VALUES (6, 3, 'shuxue', 65);

2、需求

求：所有数学课程成绩 大于 语文课程成绩的学生的学号

1、使用case…when…将不同的课程名称转换成不同的列

create view tmp_course_view asselect sid, case course when "shuxue" then score else 0 end  as shuxue,  case course when "yuwen" then score else 0 end  as yuwen from course;  select * from tmp_course_view;

2、以sid分组合并取各成绩最大值

create view tmp_course_view1 asselect aa.sid, max(aa.shuxue) as shuxue, max(aa.yuwen) as yuwen from tmp_course_view aa group by sid;  select * from tmp_course_view1;

3、比较结果

select * from tmp_course_view1 where shuxue > yuwen;

4、另外几种思路

可以用自连接来实现，确实更快速。

select c.sid from course c join course d on c.sid = d.sid and c.course ='shuxue' and d.course='yuwen' where c.score > d.score;

select sidfrom (select sid,sum(case course when "shuxue" then score else 0 end) as math,sum(case course when "yuwen" then score else 0 end) as chinesefrom course group by sid) as t1where t1.math>t1.chinese;

selectsidfrom coursegroup by sidhaving sum(case when course='yuwen' then scorewhen course='shuxue' then 0-scoreend) < 0;

结果都是：

+------+--+| sid  |+------+--+| 1    || 2    |+------+--+