HQL,SQL刷题，尚硅谷（初级）

目录

相关表数据：

题目及思路解析：

1、查询有两门以上的课程不及格的同学的学号及其平均成绩

2、查询所有学生的学号、姓名、选课数、总成绩

3、查询平均成绩大于85的所有学生的学号、姓名和平均成绩

4、查询学生的选课情况：学号，姓名，课程号，课程名称

5、查询出每门课程的及格人数和不及格人数

6、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名及课程信息

总结归纳： 

知识补充：

---

相关表数据：

1. Score_info
2. Student_info表
3. Course_info表

题目及思路解析：

1、查询有两门以上的课程不及格的同学的学号及其平均成绩

代码1：

select
    t1.stu_id,
    t2.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 = t2.stu_id;

代码1：

select
    stu_id,
    avg_score
from (
    select
        stu_id,
        avg(score) avg_score,
        sum(if(score<60,1,0)) flag
   from score_info
   group by stu_id
    )t1
where flag>=2;

思路：

这里关键是用到了sum(if()函数

代码1，首先是求出选课2门以上的学生，接着求出对应学生的平均成绩，最后将两个子查询结果表进行join拼接。总的来说，思路比较清晰，不过略显冗杂。

代码2，直接一个表求出选课2门以上的学生和对应学生的平均成绩，最后筛选得出结果。

比较简洁，不过可能不太好理解。

结果：

2、查询所有学生的学号、姓名、选课数、总成绩

代码：

  select
        st.stu_id,
        st.stu_name,
        count(course_id) count_course,
        sum(score) sum_score
    from score_info sc
    right join student_info st
    on sc.stu_id=st.stu_id
    group by st.stu_id,st.stu_name;

思路：

简单查询，这里关键是 ‘所有学生’，即我们需要显示没有选课、成绩的的学生显示信息

因此，连接student_info表时，要以student_info表为核心，即需要left join或right join

结果：

3、查询平均成绩大于85的所有学生的学号、姓名和平均成绩

代码：

 select
        stu_id,
        stu_name,
        avg(score) avg_score
    from score_info sc
    join student_info st
    on sc.stu_id=st.stu_id
    group by st.stu_id,stu_name
    having avg_score>85;

思路：

普通的查询，连接可以Join可以left join,结果一样，不过严谨一点可以用left join

结果：

4、查询学生的选课情况：学号，姓名，课程号，课程名称

代码：

select
    sc.stu_id,
    stu_name,
    sc.course_id,
    course_name
from score_info sc
left join course_info co
on sc.course_id=co.course_id

left join student_info st
on sc.stu_id=st.stu_id;

思路：

这里根据题意及表信息，因此需要连接多表，直接join也可以，结果一样。

结果：

5、查询出每门课程的及格人数和不及格人数

代码1：

select
    t1.course_id,
    co.course_name,
    t1.`及格人数`,
    t1.`不及格人数`
from
    (
    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
join course_info co
on co.course_id=t1.course_id;

代码2：

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;

思路：

这里关键还是sum(if())函数

代码1与代码2的差别不大，看个人喜欢

代码1是逻辑上，先进行嵌套子查询，然后Join连接couse_info表

代码2是Join连接子查询的结果表

结果：

6、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名及课程信息

代码1：

select
    sc.stu_id,
    score,
    stu_name,
    sc.course_id,
    course_name
from score_info sc
left join student_info st
on sc.stu_id=st.stu_id
left join course_info ci
on sc.course_id = ci.course_id
where score>80 and sc.course_id='03';

代码2： 

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与代码2的差别不大，看个人喜欢

代码1,是连接student_info与couse_info表，然后按照题意筛选信息，最后显示结果

代码2，是Join连接子查询按照题意筛选信息，然后连接couse_info表,最后显示结果

结果：

总结归纳： 

总体来说不太难，考察嵌套子查询，join连接，聚合函数的使用等

知识补充：

关于sum(if())函数

sum(if()：有条件累加,常用于分类筛选统计
 sum(if)只试用于单个条件判断,如果筛选条件很多,我们可以用sum(case when then else end)来进行多条件筛选

注意,hive中并没有sum(distinct col1)这种使用方式,我们可以使用sum(col) group by col来达到相同效果.