分组排名
“成绩表”记录了学生的名字,课程,学期以及对应的成绩。现在需要查找每学期每门课程的前3高成绩对应的学期,课程,姓名和分数。
题目属于分组排名类型,专用窗口函数包括rank, dense_rank, row_number,函数存在区别
1. 考虑3种情况
如果出现两人并列第一的情况,则同为第1名;且第3人名次为2;则使用dense_rank();
结果是1,1,2,3
如果出现两人并列第一,则同为第1名,但第3人名次为3,则使用ranK();
结果是1,1,3 ,4
如果出现两人并列第一,则不考虑并列名次,排名不断增加;即使用row_number()
结果是1,2,3,4
use sql练习;
select*,rank() over (partition by class,course order by score desc)as rank1,
dense_rank() over (partition by class,course order by score desc)as rank2,
row_number() over (partition by class,course order by score desc)as rank3
from course
2. 选择前3且不并列
use sql练习;
select class,course,stu_name,score,ranking from ( select class, course, stu_name, score,
row_number() over ( partition by class, course order by score desc ) as ranking
from sql练习.course ) as x
where x.ranking <= 3
## 字段名不要使用特殊符号
3. 名次改变
判断第二个学期中前三学生名次在两个学期的变化,并输出“进步一名”,“后退一名”,“退出前三”,“进步两名”等信息;
use sql练习;
select y1.course,y1.stu_name,
(case when cast(y1.ranking as signed) - CAST(y2.ranking AS SIGNED) = 0 then '位次不变'
when cast(y1.ranking as signed) - CAST(y2.ranking AS SIGNED) = -1 then '进步一名'
when cast(y1.ranking as signed) - CAST(y2.ranking AS SIGNED) =