sql练习:不及格课程数大于2的学生的平均成绩及其排名

有学生每科科目成绩，求不及格课程数大于2的学生的平均成绩及其成绩平均值后所在的排名。


CREATE TABLE t6_scores (
sid bigint COMMENT '学生ID',
cid bigint COMMENT '课程ID',
score bigint COMMENT '得分'
) COMMENT '用户课程分数';
-- 插入数据
insert into t6_scores(sid,cid,score)
values
(1,1,90),
(1,2,50),
(1,3,72),
(2,1,40),
(2,2,50),
(2,3,22),
(3,1,30),
(3,2,50),
(3,3,52),
(4,1,90),
(4,2,90),
(4,3,72)

1.使用聚合函数计算出每个学生的平均成绩、不及格科目数

select sid,avg(score)                                  as avg_score,sum(case when score < 60 then 1 else 0 end) as fail_num
from t6_scores
group by sid

2.根据平均成绩计算排名

DENSE_RANK() 是 MySQL 中的一个窗口函数，用于为结果集中的每一行分配一个排名，这个排名是连续的，即使有相同的排名值也不会出现间隔。这个函数在分区或结果集中的每一行分配排名时，相同的值会得到相同的排名，而且下一个不同的值会继续按顺序排名，不会有间断

select sid,avg_score,fail_num,dense_rank() over (order by avg_score desc) as rn
from (select sid,avg(score)                                  as avg_score,sum(case when score < 60 then 1 else 0 end) as fail_numfrom t6_scoresgroup by sid) t

3.得到最终结果

select sid,avg_score,rn
from (select sid,avg_score,fail_num,dense_rank() over (order by avg_score desc) as rnfrom (select sid,avg(score)                                  as avg_score,sum(case when score < 60 then 1 else 0 end) as fail_numfrom t6_scoresgroup by sid) t) tt
where fail_num > 2