目录
相关表数据：
编辑
题目及思路解析：
复杂查询，子查询
1、查询所有课程成绩均小于60分的学生的学号、姓名
2、查询没有学全所有课的学生的学号、姓名
3、查询出只选修了三门课程的全部学生的学号和姓名
总结归纳：
知识补充：
相关表数据：
1、student_info
2、score_info
3、course_info
题目及思路解析：
复杂查询，子查询
1、查询所有课程成绩均小于60分的学生的学号、姓名
tips：这里关键在所有课程，可以用sum(if())函数方法
代码1：
select
t1.stu_id,
stu_name
from
(
select
stu_id,
sum(if(score<60,0,1)) flag
from score_info
group by stu_id
having flag=0
)t1
join student_info
on t1.stu_id=student_info.stu_id;思路1：
这个解法比较常规。首先第一层（里面的子查询）按照学号分组，得到每个学生所有课程考试成绩，然后用sum(if())方法判断求和，相当于判断每个课程的成绩，小于60返回0，反之返回1，最终统计所有课程sum=0的即所有课程均小于60，最后嵌套第二层连接student_info得到学生姓名，输出结果
代码2：
select
t1.stu_id,
stu_name
from(
select
stu_id,
max(score) max_score
from score_info
group by stu_id
having max_score<60
)t1
join student_info
on t1.stu_id=student_info.stu_id;思路2：
这个解法就比较妙了。关键在第一层（子查询），同样式分组得到每个学生所有课程考试成绩，但是它先求出每个学生的所有成绩中最高分的，然后筛选最高分中小于60（最高分都小于60了，其它也一定小于60），这样就可以得到全部课程成绩均小于60
结果：
2、查询没有学全所有课的学生的学号、姓名
代码1：
select
distinct t1.stu_id,
stu_name
from (
select
stu_id
from score_info
group by stu_id,course_id
having count(course_id)<
(
select count(course_id)
from course_info )
)t1
join student_info
on t1.stu_id=student_info.stu_id;代码2:
select
s.stu_id,
s.stu_name
from student_info s
left join score_info sc on s.stu_id = sc.stu_id
group by s.stu_id, s.stu_name
having count(course_id) < (select count(course_id) from course_info);思路：
代码1两层嵌套，逻辑比较清晰吧，首先第一层获取从course_info表总课程数，接着第二层查询score_info表 按照学号和课程ID分组，得到每个学生所有课程考试成绩，筛选课程数小于总课程数的学生，然后第三层Join student_info表得到学生姓名，最后输出结果（需要去重）
代码2一层嵌套，不同点在于它查询的是studen_info表，然后join score_info表,筛选部分一样
总的来说，代码2比较简洁一点
结果：
3、查询出只选修了三门课程的全部学生的学号和姓名
代码1：
select
t1.stu_id,
stu_name
from (
select
stu_id
from score_info
group by stu_id
having count(course_id)=3
)t1
join student_info
on t1.stu_id=student_info.stu_id;代码2：
select
s.stu_id,
s.stu_name
from student_info s
join (
select
stu_id,
count(course_id) course_count
from score_info
group by stu_id
having course_count =3
) t1
on s.stu_id = t1.stu_id;思路：
这题比较简单。
代码1 ，首先第一层按照stu_id分组，得到每个学生所有课程考试成绩，然后统计course_id数=3的学生，然后第二层连接student_info表，输出结果
代码2主要不同在于它把第一层查询表结果直接与student_info表join连接
结果：
总结归纳：
这几道题主要是考察复杂查询的子查询， Join连接等
知识补充：
关于sum(if())
- sum(if()：有条件累加,常用于分类筛选统计
sum(if)只试用于单个条件判断,如果筛选条件很多,我们可以用sum(case when then else end)来进行多条件筛选
注意,hive中并没有sum(distinct col1)这种使用方式,我们可以使用sum(col) group by col来达到相同效果.
