Hive实战之学生课程成绩

 

---

基表：

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;