HQL,SQL刷题,尚硅谷(初级)

文章详细解析了如何使用SQL子查询和Join连接解决三个问题:1)找出所有课程成绩均小于60分的学生;2)查询未选修所有课程的学生;3)获取只选修三门课程的学生。同时介绍了sum(if())函数在条件统计中的应用。
摘要由CSDN通过智能技术生成

目录

相关表数据:

​编辑

题目及思路解析:

复杂查询,子查询

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来达到相同效果.

  • 11
    点赞
  • 11
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值