基表:
use myhive;CREATE TABLE `course` (
`id` int,
`sid` int ,
`course` string,
`score` int
) ;
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);
需求:所有数学课程成绩 大于 语文课程成绩的学生的学号。
实现需求步骤;
1,使用case...when...将不同的课程名称转换成不同的列。
create view tmp as select 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;
2,以sid分组并获取个成绩的最大值
create view tmp_view
as select sid as sid ,max(shuxue) as shuxue ,max(yuwen) as yuwen
from tmp group by sid;
3,比较结果
select * from tmp_view where shuxue>yuwen;