hive面试题之二

// 建表语句：
CREATE TABLE `course` (
`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`sid` int(11) DEFAULT NULL,
`course` varchar(255) DEFAULT NULL,
`score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

// 插入数据
// 字段解释：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);
INSERT INTO `course` VALUES (7, 3, 'yingyu', 80);

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

原始数据：
id | sid  | course | score |
+----+------+--------+-------+
|  1 |    1 | yuwen  |    43 |
|  2 |    1 | shuxue |    55 |
|  3 |    2 | yuwen  |    77 |
|  4 |    2 | shuxue |    88 |
|  5 |    3 | yuwen  |    98 |
|  6 |    3 | shuxue |    65 |

比较时候  同一行中的不同列进行比较
语文  和数学  两行

数据:
id | sid  | yuwen | shuxue |
  1 |    1 |34      | 55     |
  
java:
    int user_input=....;
    switch(条件判断){
        case1:
            break;
        case 2:
        
        default:
            
    }
条件判断的：
CASE 判断的变量 
WHEN 条件1 THEN 返回值 
ELSE 不满足条件的返回值 END


id | sid  | course | score |
+----+------+--------+-------+
|  1 |    1 | yuwen  |    43 |

如果course字段是语文  返回语文成绩  否则返回0

select 
case course 
when "yuwen" then score else 0 end 
from course;

1)得到下面的数据
id | sid  | yuwen | shuxue |
+----+------+--------+-------+
|  1 |    1 | 43  |    0 |
|  2 |    1 | 0 |    55 |
|  3 |    2 | 77  |    0 |
|  4 |    2 | 0 |    88 |
|  5 |    3 | 98  |    0 |
|  6 |    3 | 0 |    65 |


id | sid  | course | score |
+----+------+--------+-------+
|  1 |    1 | yuwen  |    43 |


select 
sid,
case course when "yuwen" then score else 0 end yuwen,
case course when "shuxue" then score else 0 end shuxue 
from course;

结果：
+------+-------+--------+
| sid  | yuwen | shuxue |
+------+-------+--------+
|    1 |    43 |      0 |
|    1 |     0 |     55 |
|    2 |    77 |      0 |
|    2 |     0 |     88 |
|    3 |    98 |      0 |
|    3 |     0 |     65 |
+------+-------+--------+
2）上面结果的同一个人的数学  语文放在一行
分组：sid  求max  sum

select 
sid,max(yuwen) yuwen,max(shuxue) shuxue 
from (select 
sid,
case course when "yuwen" then score else 0 end yuwen,
case course when "shuxue" then score else 0 end shuxue 
from course) a group by sid;

| sid  | yuwen | shuxue |
+------+-------+--------+
|    1 |    43 |     55 |
|    2 |    77 |     88 |
|    3 |    98 |     65 |


3)最终结果
过滤：yuwen<shuxue
select 
b.sid sid,b.yuwen yuwen,b.shuxue shuxue 
from (select 
sid,max(yuwen) yuwen,max(shuxue) shuxue 
from (select 
sid,
case course when "yuwen" then score else 0 end yuwen,
case course when "shuxue" then score else 0 end shuxue 
from course) a group by sid) b where b.yuwen<b.shuxue;

| sid  | yuwen | shuxue |
+------+-------+--------+
|    1 |    43 |     55 |
|    2 |    77 |     88 |