相关表数据:
- Score_info表
2、Student_info表
3、Course_info表
4、Teacher_info 表
题目及思路解析:
多表连接
题目:查询所有课程成绩在70分以上的学生的姓名、课程名称和分数,按分数升序排列
这道题的题目可能会被理解为两种意思,第一种,学生成绩在70分以上的所有学生,第二种学生的所有课程成绩在70分以上的学生。
第一种:
select
sc.stu_id,
stu_name,
course_name,
score
from score_info sc
left join student_info st on st.stu_id = sc.stu_id
left join course_info ci on sc.course_id = ci.course_id
where score>70;
显然,按照第一种想法来做那就太简单了(放在我之前刷的那部分简单题目倒是可以),而且这种想法忽略了关键字“课程” ,所以pass掉,选第二种。
第二种:
代码:
select
s.stu_id,
s.stu_name,
c.course_name,
s2.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 = s2.stu_id
left join course_info c on s2.course_id = c.course_id;
思路解析:
这道题关键是要怎么找出学生所有课程且成绩70以上
代码核心在子查询部分,sum(if()函数,这里不能像之前那样统计个数,因为我们不能确定每个学生的所有课程数,而且课程数也不确定,因此,我们可以换一种思路,只要一门课程成绩70以上,则置为0,最后筛选,只要全部成绩>=70,那么sum(if())必定=0。
另外,在这里需要分组,一方面方便统计,去重,另一方面则是设置筛选条件(聚合函数),其他就是简单join连接了。
说明补充:
这里可能有同学觉得子查询用了score_info表,Join连接又使用score_info表,太过于冗余,可不可以直接用子查询作为主表(即from 子查询),然后join course_info表和student_info表就好了。
实际上是不可以的,因为子查询只能获取stu_id,如果加上score,那么sum(if()就会筛选出(任意一科课程)成绩70以上的学生
知识补充:
---关于sum(if())函数
sum(if():有条件累加,常用于分类筛选统计
sum(if)只试用于单个条件判断,如果筛选条件很多,我们可以用sum(case when then else end)来进行多条件筛选注意,hive中并没有sum(distinct col1)这种使用方式,我们可以使用sum(col) group by col来达到相同效果.