Hive实战之学生课程成绩

基表：

1use myhive;CREATE TABLE `course` (
2  `id` int,
3  `sid` int ,
4  `course` string,
5  `score` int 
6) ;
7
8INSERT 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...将不同的课程名称转换成不同的列。

1create view tmp as select sid ,
2case course when "shuxue" then score else 0 end as shuxue,
3case course when "yuwen" then score else 0 end as yuwen 
4from  course;

　　2,以sid分组并获取个成绩的最大值

1create view tmp_view 
2as select sid as sid ,max(shuxue) as shuxue ,max(yuwen) as yuwen
3from tmp group by sid;

　　3,比较结果

select  * from tmp_view where shuxue>yuwen;