50道HQL题

1

mkdir /home/forsre/hql && cd /home/forsre/hql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

tee student.csv <<-'EOF'
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,女                   
EOF

1
2
3
4
5

tee course.csv <<-"EOF"
01,语文,02
02,数学,01
03,英语,03
EOF

1
2
3
4
5

tee teacher.csv <<-"EOF"
01,张三
02,李四
03,王五
EOF

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

tee score.csv <<-"EOF"
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
EOF

1
2
3
4
5
6
7

create database hql;
use hql;

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 ',';

1
2
3
4

load data local inpath '/home/forsre/hql/student.csv' into table student;
load data local inpath '/home/forsre/hql/course.csv' into table course;
load data local inpath '/home/forsre/hql/teacher.csv' into table teacher;
load data local inpath '/home/forsre/hql/score.csv' into table score;

1
2

#对齐表头或者通过dbeaver或者datagrip
beeline -u jdbc:hive2://hadoop161:10000 -n forsre

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

select stu.*, 01_score.s_score as 01_score, 02_score.s_score as 02_score
from student as stu
join score as 01_score 
on stu.s_id=01_score.s_id 
and 01_score.c_id='01'
left join score as 02_score 
on stu.s_id=02_score.s_id
and 02_score.c_id='02'
where 01_score.s_score > 02_score.s_score
or 02_score.s_score is null and 01_score.s_score is not null;

/*
+-----------+-------------+--------------+------------+-------------------+-------------------+--+
| stu.s_id  | stu.s_name  | stu.s_birth  | stu.s_sex  | 01_score  | 02_score  |
+-----------+-------------+--------------+------------+-------------------+-------------------+--+
| 02        | 钱电          | 1990-12-21   | 男          | 70                | 60                |
| 04        | 李云          | 1990-08-06   | 男          | 50                | 30                |
| 06        | 吴兰          | 1992-03-01   | 女          | 31                | NULL              |
+-----------+-------------+--------------+------------+-------------------+-------------------+--+
3 rows selected (15.03 seconds)
*/

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

select stu.*, 01_score.s_score, 02_score.s_score
from student as stu
full outer join score as 01_score 
on stu.s_id=01_score.s_id 
and 01_score.c_id='01'
left join score as 02_score 
on stu.s_id=02_score.s_id
and 02_score.c_id='02'
where 01_score.s_score < 02_score.s_score
or 01_score.s_score is null and 02_score.s_score is not null
order by s_id;

/*


*/

1