目录

相关表数据：

​编辑

题目及思路解析：

复杂查询，子查询

1、查询所有课程成绩均小于60分的学生的学号、姓名

2、查询没有学全所有课的学生的学号、姓名

3、查询出只选修了三门课程的全部学生的学号和姓名

总结归纳：

知识补充：

---

相关表数据：

1、student_info

2、score_info

3、course_info

题目及思路解析：

复杂查询，子查询

1、查询所有课程成绩均小于60分的学生的学号、姓名

tips：这里关键在所有课程，可以用sum(if())函数方法

代码1：

selectt1.stu_id,stu_name
from(selectstu_id,sum(if(score<60,0,1)) flagfrom score_infogroup by stu_idhaving 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：

selectt1.stu_id,stu_namefrom(selectstu_id,max(score) max_scorefrom score_infogroup by stu_idhaving max_score<60)t1join student_infoon t1.stu_id=student_info.stu_id;

思路2：

这个解法就比较妙了。关键在第一层（子查询），同样式分组得到每个学生所有课程考试成绩，但是它先求出每个学生的所有成绩中最高分的，然后筛选最高分中小于60（最高分都小于60了，其它也一定小于60），这样就可以得到全部课程成绩均小于60

结果：

2、查询没有学全所有课的学生的学号、姓名

代码1：

selectdistinct t1.stu_id,stu_name
from (selectstu_idfrom score_infogroup by stu_id,course_idhaving count(course_id)<(select count(course_id)from course_info ))t1
join student_info
on t1.stu_id=student_info.stu_id;

代码2:

selects.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：

selectt1.stu_id,stu_name
from (selectstu_idfrom score_infogroup by stu_idhaving count(course_id)=3)t1
join student_info
on t1.stu_id=student_info.stu_id;

代码2：

selects.stu_id,s.stu_name
from student_info s
join (selectstu_id,count(course_id) course_countfrom score_infogroup by stu_idhaving 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来达到相同效果.