实现每班前三名,分数一样并列, 同时求出前三名按名次排序的分差
当我们看到前3名首先要想到开窗函数 row_number() over()、rank()over()、dense_rank() over(),在这里我们要强调一下这三者的区别, row_number() over()按照名词排序名词不会重复即使成绩一样,rank()over()会出现并列的情况,dense_rank() over()名词跳跃。这道题要求“分数一样时并列”我们用rank()over()。
数据准备:
--创建表
create table student(
sid varchar(20),-- 学号
cid varchar(20),-- 班级号
score1 int -- 分数
);
--插入数据
insert into student values
('1','1901',90)
,('2','1901',90)
,('3','1901',83)
,('4','1901',60)
,('5','1902',66)
,('6','1902',23)
,('7','1902',99)
,('8','1902',67)
,('9','1902',87);
在这里我们使用到了lead函数来获取基于当前数据的下一条记录。上一个博客中有详细记录
https://mp.csdn.net/mp_blog/creation/editor/127857879
sql实现:
-- 实现每班前三名,分数一样并列, 同时求出前三名按
-- 名次排序的分差
select t1.sid,t1.cid,t1.score1,t1.score1-t1.nextscore from(
select sid,cid,score1,rank() over(partition by cid order by score1 desc) rk ,lead(score1,1,0) over(partition by cid order by score1 desc) nextscore from student
) t1 where t1.rk<=3
sql实现结果如下: