【博学谷学习记录】超强总结，用心分享|HiveSQL必练50题(下)

26、查询每门课程需要补考的学生数

select course.c_id, course.c_name, count(course.c_id)
from course
         join score s on course.c_id = s.c_id
where s.s_score < 60
group by course.c_id, course.c_name;

select c.c_id, c.c_name, tmp.number
from course c
         join (select c_id, count(1) as number
               from score
               where score.s_score < 60
               group by score.c_id) tmp
              on tmp.c_id = c.c_id;

27、查询出只有两门课程的全部学生的学号和姓名

select s.s_id, s.s_name, count_course
from student s
         join (select s_id, count(c_id) count_course from score group by s_id having count_course = 2) t1
              on s.s_id = t1.s_id;

28、查询男生、女生人数

select s_sex, count(*)
from student
group by s_sex;

29、查询名字中含有"风"字的学生信息

select *
from student
where s_name like '%风%';

30、查询同名同性学生名单，并统计同名人数

select *
from (select s.*, count() over (partition by s_name) count
      from student s) t
where count > 1;

31、查询1990年出生的学生名单

select *
from (select *, year(s_birth) birthyear
      from student) t
where birthyear = 1990;

select *
from student
where s_birth like '1990%';

32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

select s1.c_id, s1.c_name, round(avg(s2.s_score), 1) avg_score
from course s1
         join score s2 on s1.c_id = s2.c_id
group by s1.c_id, s1.c_name
order by avg_score desc, s1.c_id asc;

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

select s.s_id, s.s_name, avg(s2.s_score) avg_score
from student s
         join score s2 on s.s_id = s2.s_id
group by s.s_id, s.s_name;

34、查询课程名称为"数学"，且分数低于60的学生姓名和分数

select s.s_name, s2.s_score, c.c_name
from student s
         join score s2 on s.s_id = s2.s_id
         join course c on s2.c_id = c.c_id
where c.c_name = '数学'
  and s2.s_score < 60;

35、查询学生所有的课程及分数情况

select s.s_name,
       sum(case when c.c_name = '语文' then s2.s_score else 0 end) chinese,
       sum(case when c.c_name = '数学' then s2.s_score else 0 end) math,
       sum(case when c.c_name = '英语' then s2.s_score else 0 end) english,
       sum(s2.s_score)                                             sum_score
from student s
         join score s2 on s.s_id = s2.s_id
         join course c on s2.c_id = c.c_id
group by s.s_name;

36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数

select s.s_name, c.c_name, s2.s_score
from student s
         join score s2 on s.s_id = s2.s_id
         join course c on s2.c_id = c.c_id
where s2.s_score >= 70;

37、查询课程不及格的学生

select s.s_name, c.c_name, s2.s_score
from student s
         join score s2 on s.s_id = s2.s_id
         join course c on s2.c_id = c.c_id
where s2.s_score < 60;

38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名

select s.s_id, s.s_name, s2.c_id, s2.s_score
from student s
         join score s2 on s.s_id = s2.s_id
where s2.c_id = '01'
  and s2.s_score >= 80;

39、求每门课程的学生人数

select c.c_name, count(s.c_id)
from course c
         join score s on c.c_id = s.c_id
group by c.c_name;

40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

select *
from (select s.*, s2.s_score, c.c_name, row_number() over (order by s2.s_score desc ) rk
      from student s
               right join score s2 on s.s_id = s2.s_id
               right join course c on s2.c_id = c.c_id
               right join teacher t on c.t_id = t.t_id and t.t_name = '张三') t
where rk = 1;

select student.*, tmp3.c_name, tmp3.maxScore
from (select s_id, c_name, max(s_score) as maxScore
      from score
               join (select course.c_id, c_name
                     from course
                              join
                              (select t_id, t_name from teacher where t_name = '张三') tmp
                              on course.t_id = tmp.t_id) tmp2
                    on score.c_id = tmp2.c_id
      group by score.s_id, c_name
      order by maxScore desc
      limit 1) tmp3
         join student
              on student.s_id = tmp3.s_id;

41、查询课程不同,但是成绩相同的学生的学生编号、课程编号、学生成绩

select a.s_id, a.c_id, a.s_score
from score a,
     score b
where a.c_id != b.c_id
  and a.s_score = b.s_score
group by a.s_id, a.c_id, a.s_score;

select distinct a.s_id, a.c_id, a.s_score
from score a,
     score b
where a.c_id <> b.c_id
  and a.s_score = b.s_score;

42、查询每门课程成绩最好的前两名

select *
from (select *,
             row_number() over (partition by c_id order by s_score desc) rk
      from score) t
where rk <= 2;

43、统计每门课程的学生选修人数（超过5人的课程才统计）

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select c_id, count(s_id) stu
from score
group by c_id
having stu > 5
order by stu desc, c_id;

44、检索至少选修两门课程的学生学号

select s_id, count(c_id) count
from score
group by s_id
having count >= 2;

45、查询选修了全部课程的学生信息

select *
from student s
         join (select s_id, count(c_id) count
               from score
               group by s_id
               having count = 3) t on s.s_id = t.s_id;

46、查询各学生的年龄(周岁), 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

datediff(current_date(),s_birth) 计算当前日期与学生的出生日期相差天数
floor((year(current_date) - year(s_birth))/4) 计算润年数量
(datediff(current_date(),s_birth)-floor((year(current_date) - year(s_birth))/4)) 润年会多一天，需要减去润年数量x天数

select floor((datediff(`current_date`(),s_birth)-floor((year(current_date) - year(s_birth))/4))/365) from student;

47、查询本周过生日的学生

select * from student where weekofyear(`current_date`()) = weekofyear(s_birth);

48、查询下周过生日的学生

select * from student where weekofyear(`current_date`())+1 = weekofyear(s_birth);

49、查询本月过生日的学生

select * from student where month(`current_date`()) = month(s_birth);

50、查询12月份过生日的学生

select * from student where month(s_birth) = 12;