前言

50道Hive之sql必练题，做完50道题会对HiveSql有一个比较深的认识，且附上答案以及验证结果。

建表

-- 学生表
create table student(s_id string,s_name string,s_birth string,s_sex string) row format delimited fields terminated by '\,';
-- 课堂表
create table course(c_id string,c_name string,t_id string) row format delimited fields terminated by '\,';
-- 教师表
create table teacher(t_id string,t_name string) row format delimited fields terminated by '\,';
-- 分数表
create table score(s_id string,c_id string,s_score int) row format delimited fields terminated by '\,';

数据

-- vim /opt/module/data/student.csv
01,赵雷,1990-01-01,男
02,钱电,1990-12-21,男
03,孙风,1990-05-20,男
04,李云,1990-08-06,男
05,周梅,1991-12-01,女
06,吴兰,1992-03-01,女
07,郑竹,1989-07-01,女
08,王菊,1990-01-20,女
-- vim /opt/module/datas/course.csv
01,语文,02
02,数学,01
03,英语,03
-- vim /opt/module/datas/teacher.csv
01,张三
02,李四
03,王五
-- vim /opt/module/datas/score.csv
01,01,80
01,02,90
01,03,99
02,01,70
02,02,60
02,03,80
03,01,80
03,02,80
03,03,80
04,01,50
04,02,30
04,03,20
05,01,76
05,02,87
06,01,31
06,03,34
07,02,89
07,03,98

加载

load data local inpath '/opt/module/datas/student.csv' into table student;
load data local inpath '/opt/module/datas/course.csv' into table course;
load data local inpath '/opt/module/datas/teacher.csv' into table teacher;
load data local inpath '/opt/module/datas/score.csv' into table score;

1 查询”01”课程比”02”课程成绩高的学生的信息及课程分数

解析：通过s_id关联，是学生的唯一id

​ inner join = join

select student.*,a.s_score as 01_score,b.s_score as 02_score
from student
  join score a on student.s_id=a.s_id and a.c_id='01'
  left join score b on student.s_id=b.s_id and b.c_id='02'
where  a.s_score>b.s_score;

+---------------+-----------------+------------------+----------------+-----------+-----------+
| student.s_id  | student.s_name  | student.s_birth  | student.s_sex  | 01_score  | 02_score  |
+---------------+-----------------+------------------+----------------+-----------+-----------+
| 02            | 钱电              | 1990-12-21       | 男              | 70        | 60        |
| 04            | 李云              | 1990-08-06       | 男              | 50        | 30        |
+---------------+-----------------+------------------+----------------+-----------+-----------+