第12周 · hive sql综合训练（二）
综合查询训练二：复杂查询与多表查询
本实训对应的知识点
- 单元5 5.1 分析SELECT语句 · Hive数据查询
- 单元5 5.2 执行条件查询 · Hive数据查询
- 单元5 5.3 执行公共表表达式查询 · Hive数据查询
- 单元5 5.4 执行分组查询 · Hive数据查询
- 单元5 5.5 执行排序查询 · Hive数据查询
- 单元5 5.6 执行合并查询 · Hive数据查询
- 单元5 5.7 执行连接查询 · Hive数据查询
- 单元5 5.8 执行抽样查询 · Hive数据查询
- 单元7 7.1 优化数据存储 · Hive性能优化
- 单元7 7.2 优化配置参数 · Hive性能优化
- 单元7 7.3 优化执行语句 · Hive性能优化
实训内容与步骤 依据原始实训文档整理，命令与配置均来自文档原文
1.1多表查询
1.1.1表联结
（1）如图1-1所示，查询有两门以上的课程不及格的同学的学号及其平均成绩
① 先找出有两门以上不及格的学生名单，按照学生分组，过滤组内成绩低于60的并进行count，count>=2。
② 接着做出一张表查询学生的平均成绩并和上一个子查询中的学生学号进行连接
hive>select
t1.stu_id,
t1.avg_score
from (select
stu_id,
sum(if(score < 60,1,0)) flage
from score_info
group by stu_id
having flage >= 2
) t1join (
select
stu_id,
avg(score) avg_score
from score_info
group by stu_id
) t2 on t1.stu_id = t1.stu_id;结果
📷 图1-1 查询有两门以上的课程不及格的同学的学号及其平均成绩（2） 如图1-2所示，查询所有学生的学号、姓名、选课数、总成绩
select
s.stu_id,
s.stu_name,
count(sc.course_id) count_course,
sum(sc.score) sum_score
from student_info s
left join score_info sc on s.stu_id = sc.stu_id
group by s.stu_id,s.stu_name;结果
📷 图1-2 查询所有学生的学号、姓名、选课数、总成绩（3）如图1-3所示，查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select s.stu_id,s.stu_name,
avg(sc.score) avg_score
from score_info sc
left join student_info s on s.stu_id = sc.stu_id
group by s.stu_id, s.stu_name
having avg_score > 85结果
📷 图1-3查询平均成绩大于85的所有学生的学号、姓名和平均成绩（4） 如图1-4所示，查询学生的选课情况：学号，姓名，课程号，课程名称
select
s.stu_id,
s.stu_name,
c.course_id,
c.course_name
from score_info sc
join course_info c on sc.course_id = c.course_id
join student_info s on sc.stu_id = s.stu_id;结果
📷 图1-4查询学生的选课情况：学号，姓名，课程号，课程名称（5）如图9-5所示，查询出每门课程的及格人数和不及格人数
select
c.course_id,
c.course_name,
t1.`及格人数`,
t1.`不及格人数`
from course_info cjoin (
select
course_id,
sum(if(score >= 60,1,0)) as `及格人数`,
sum(if(score < 60,1,0)) as `不及格人数`
from score_info
group by course_id
) t1 on c.course_id = t1.course_id;结果
📷 图1-5 查询出每门课程的及格人数和不及格人数（6）如图1-6所示，查询课程编号为03且课程成绩在80分以上的学生的学号和姓名及课程信息
select
s.stu_id,
s.stu_name,
t1.score,
t1.course_id,
c.course_name
from student_info sjoin (
select
stu_id,
score,
course_id
from score_info
where score > 80 and course_id = '03'
) t1
on s.stu_id = t1.stu_id
join course_info c on c.course_id = t1.course_id;结果
📷 图1-6 查询课程编号为03且课程成绩在80分以上的学生的学号和姓名及课程信息1.1.2多表连接
（1）如图1-7，课程编号为"01"且课程分数小于60，按分数降序排列的学生信息
select
s.stu_id,
s.stu_name,
s.birthday,
s.sex,
t1.score
from student_info sjoin (
select
stu_id,
course_id,
score
📷 图1-7课程编号为"01"且课程分数小于60，按分数降序排列的学生信息from score_info
where score < 60 and course_id = '01'
) t1
on s.stu_id=t1.stu_id
order by t1.score desc;（2）如图1-8所示，查询所有课程成绩在70分以上的学生的姓名、课程名称和分数，按分数升序排列
select
s.stu_id,
s.stu_name,
c.course_name,
s1.score
from student_info sjoin (
select
stu_id,
sum(if(score >= 70,0,1)) flage
from score_info
group by stu_id
having flage =0
) t1
on s.stu_id = t1.stu_id
left join score_info s2 on s.stu_id = s1.stu_id
left join course_info c on s1.course_id = c.course_id;结果如图1-8所示：
📷 图1-8 查询所有课程成绩在70分以上的学生的姓名、课程名称和分数，按分数升序排列（3）如图1-9所示，查询该学生不同课程的成绩相同的学生编号、课程编号、学生成绩
select
sc1.stu_id,
sc1.course_id,
sc1.score
📷 图1-9查询该学生不同课程的成绩相同的学生编号、课程编号、学生成绩from score_info sc1
join score_info sc2 on sc1.stu_id = sc1.stu_id
and sc1.course_id <> sc1.course_id
and sc1.score = sc1.score;（4）如图1-10所示， 查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号
知识点：多表连接 + 条件
select
s1.stu_id
from
(
select
sc1.stu_id,
sc1.course_id,
sc1.score
from score_info sc1
where sc1.course_id ='01'
) s1join
(
select
sc1.stu_id,
sc1.course_id,
score
📷 图1-10查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号from score_info sc2
where sc1.course_id ="02"
) s2
on s1.stu_id=s1.stu_id
where s1.score > s1.score;（5）如图1-11查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名
select
t1.stu_id as `学号`,
s.stu_name as `姓名`
from
( selectstu_id
from score_info sc1
where sc1.course_id='01'
and stu_id in (select
stu_id
from score_info sc2
where sc1.course_id='02'
) )t1
join student_info s
on t1.stu_id = s.stu_id;结果如图1-11所示：
📷 图1-11 查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（6）如图1-12所示，查询学过“李体音”老师所教的所有课的同学的学号、姓名
select
t1.stu_id,
si.stu_name
from
( selectstu_id
from score_info si
where course_id in(
select
course_id
from course_info c
join teacher_info t
on c.tea_id = t.tea_idwhere tea_name='李体音' --李体音教的所有课程
)
group by stu_idhaving count(*)=2 --学习所有课程的学生
📷 图1-12 查询学过“李体音”老师所教的所有课的同学的学号、姓名)t1
left join student_info si
on t1.stu_id=si.stu_id;（7）如图1-13所示，查询学过“李体音”老师所讲授的任意一门课程的学生的学号、姓名
select
t1.stu_id,
si.stu_name
from
( selectstu_id
from score_info si
where course_id in
( selectcourse_id
from course_info c
join teacher_info t
on c.tea_id = t.tea_idwhere tea_name='李体音'
)
group by stu_id
)t1
left join student_info si
on t1.stu_id=si.stu_id;结果
📷 图1-13 查询学过“李体音”老师所讲授的任意一门课程的学生的学号、姓名（8）如图1-14所示，查询至少有一门课与学号为“001”的学生所学课程相同的学生的学号和姓名
select
si.stu_id,
si.stu_name
from score_info sc
join student_info si
on sc.stu_id = si.stu_id
where sc.course_id in(
select
course_id
from score_infowhere stu_id='001' --001的课程
) and sc.stu_id <> '001' --排除001学生
group by si.stu_id,si.stu_name;结果如图1-14所示：
📷 图1-14查询至少有一门课与学号为“001”的学生所学课程相同的学生的学号和姓名（9）如图1-15所示， 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select
si.stu_name,
ci.course_name,
sc.score,
t1.avg_score
from score_info sc
join student_info si
on sc.stu_id=si.stu_id
join course_info ci
on sc.course_id=ci.course_idjoin
(
select
stu_id,
avg(score) avg_score
from score_info
group by stu_id
) t1
on sc.stu_id=t1.stu_id
order by t1.avg_score desc;结果
📷 图1-15 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩II
📄 展开原始文档全文（276 段）
hive sql综合训练（二）
1.1 多表查询
1.1.1 表联结
（1）如图1-1所示，查询有两门以上的课程不及格的同学的学号及其平均成绩
① 先找出有两门以上不及格的学生名单，按照学生分组，过滤组内成绩低于60的并进行count，count>=2。
② 接着做出一张表查询学生的平均成绩并和上一个子查询中的学生学号进行连接
hive>
select
t1.stu_id,
t1.avg_score
from (
select
stu_id,
sum(if(score < 60,1,0)) flage
from score_info
group by stu_id
having flage >= 2
) t1
join (
select
stu_id,
avg(score) avg_score
from score_info
group by stu_id
) t2 on t1.stu_id = t1.stu_id;
结果
📷 图1-1 查询有两门以上的课程不及格的同学的学号及其平均成绩
（2） 如图1-2所示，查询所有学生的学号、姓名、选课数、总成绩
select
s.stu_id,
s.stu_name,
count(sc.course_id) count_course,
sum(sc.score) sum_score
from student_info s
left join score_info sc on s.stu_id = sc.stu_id
group by s.stu_id,s.stu_name;
结果
📷 图1-2 查询所有学生的学号、姓名、选课数、总成绩
（3）如图1-3所示，查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select s.stu_id,
s.stu_name,
avg(sc.score) avg_score
from score_info sc
left join student_info s on s.stu_id = sc.stu_id
group by s.stu_id, s.stu_name
having avg_score > 85
结果
📷 图1-3查询平均成绩大于85的所有学生的学号、姓名和平均成绩
（4） 如图1-4所示，查询学生的选课情况：学号，姓名，课程号，课程名称
select
s.stu_id,
s.stu_name,
c.course_id,
c.course_name
from score_info sc
join course_info c on sc.course_id = c.course_id
join student_info s on sc.stu_id = s.stu_id;
结果
📷 图1-4查询学生的选课情况：学号，姓名，课程号，课程名称
（5）如图9-5所示，查询出每门课程的及格人数和不及格人数
select
c.course_id,
c.course_name,
t1.`及格人数`,
t1.`不及格人数`
from course_info c
join (
select
course_id,
sum(if(score >= 60,1,0)) as `及格人数`,
sum(if(score < 60,1,0)) as `不及格人数`
from score_info
group by course_id
) t1 on c.course_id = t1.course_id;
结果
📷 图1-5 查询出每门课程的及格人数和不及格人数
（6）如图1-6所示，查询课程编号为03且课程成绩在80分以上的学生的学号和姓名及课程信息
select
s.stu_id,
s.stu_name,
t1.score,
t1.course_id,
c.course_name
from student_info s
join (
select
stu_id,
score,
course_id
from score_info
where score > 80 and course_id = '03'
) t1
on s.stu_id = t1.stu_id
join course_info c on c.course_id = t1.course_id;
结果
📷 图1-6 查询课程编号为03且课程成绩在80分以上的学生的学号和姓名及课程信息
1.1.2 多表连接
（1）如图1-7，课程编号为"01"且课程分数小于60，按分数降序排列的学生信息
select
s.stu_id,
s.stu_name,
s.birthday,
s.sex,
t1.score
from student_info s
join (
select
stu_id,
course_id,
score
from score_info
where score < 60 and course_id = '01'
) t1
on s.stu_id=t1.stu_id
order by t1.score desc;
📷 图1-7课程编号为"01"且课程分数小于60，按分数降序排列的学生信息
（2）如图1-8所示，查询所有课程成绩在70分以上的学生的姓名、课程名称和分数，按分数升序排列
select
s.stu_id,
s.stu_name,
c.course_name,
s1.score
from student_info s
join (
select
stu_id,
sum(if(score >= 70,0,1)) flage
from score_info
group by stu_id
having flage =0
) t1
on s.stu_id = t1.stu_id
left join score_info s2 on s.stu_id = s1.stu_id
left join course_info c on s1.course_id = c.course_id;
结果如图1-8所示：
📷 图1-8 查询所有课程成绩在70分以上的学生的姓名、课程名称和分数，按分数升序排列
（3）如图1-9所示，查询该学生不同课程的成绩相同的学生编号、课程编号、学生成绩
select
sc1.stu_id,
sc1.course_id,
sc1.score
from score_info sc1
join score_info sc2 on sc1.stu_id = sc1.stu_id
and sc1.course_id <> sc1.course_id
and sc1.score = sc1.score;
📷 图1-9查询该学生不同课程的成绩相同的学生编号、课程编号、学生成绩
（4）如图1-10所示， 查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号
知识点：多表连接 + 条件
select
s1.stu_id
from
(
select
sc1.stu_id,
sc1.course_id,
sc1.score
from score_info sc1
where sc1.course_id ='01'
) s1
join
(
select
sc1.stu_id,
sc1.course_id,
score
from score_info sc2
where sc1.course_id ="02"
) s2
on s1.stu_id=s1.stu_id
where s1.score > s1.score;
📷 图1-10查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号
（5）如图1-11查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名
select
t1.stu_id as `学号`,
s.stu_name as `姓名`
from
( select
stu_id
from score_info sc1
where sc1.course_id='01'
and stu_id in (
select
stu_id
from score_info sc2
where sc1.course_id='02'
) )t1
join student_info s
on t1.stu_id = s.stu_id;
结果如图1-11所示：
📷 图1-11 查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名
（6）如图1-12所示，查询学过“李体音”老师所教的所有课的同学的学号、姓名
select
t1.stu_id,
si.stu_name
from
( select
stu_id
from score_info si
where course_id in
(
select
course_id
from course_info c
join teacher_info t
on c.tea_id = t.tea_id
where tea_name='李体音' --李体音教的所有课程
)
group by stu_id
having count(*)=2 --学习所有课程的学生
)t1
left join student_info si
on t1.stu_id=si.stu_id;
📷 图1-12 查询学过“李体音”老师所教的所有课的同学的学号、姓名
（7）如图1-13所示，查询学过“李体音”老师所讲授的任意一门课程的学生的学号、姓名
select
t1.stu_id,
si.stu_name
from
( select
stu_id
from score_info si
where course_id in
( select
course_id
from course_info c
join teacher_info t
on c.tea_id = t.tea_id
where tea_name='李体音'
)
group by stu_id
)t1
left join student_info si
on t1.stu_id=si.stu_id;
结果
📷 图1-13 查询学过“李体音”老师所讲授的任意一门课程的学生的学号、姓名
（8）如图1-14所示，查询至少有一门课与学号为“001”的学生所学课程相同的学生的学号和姓名
select
si.stu_id,
si.stu_name
from score_info sc
join student_info si
on sc.stu_id = si.stu_id
where sc.course_id in
(
select
course_id
from score_info
where stu_id='001' --001的课程
) and sc.stu_id <> '001' --排除001学生
group by si.stu_id,si.stu_name;
结果如图1-14所示：
📷 图1-14查询至少有一门课与学号为“001”的学生所学课程相同的学生的学号和姓名
（9）如图1-15所示， 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select
si.stu_name,
ci.course_name,
sc.score,
t1.avg_score
from score_info sc
join student_info si
on sc.stu_id=si.stu_id
join course_info ci
on sc.course_id=ci.course_id
join
(
select
stu_id,
avg(score) avg_score
from score_info
group by stu_id
) t1
on sc.stu_id=t1.stu_id
order by t1.avg_score desc;
结果
📷 图1-15 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
II