三种常见的排名
row_number、dense_rank、rank在MySQL 5.7中的实现
准备数据
表结构说明
成绩表 SC(SId,CId,score)
- SId 学⽣编号
- CId 课程编号
- score 分数
创建SC表
create table SC(
SId varchar(10),
CId varchar(10),
score decimal(18,1)
);
插入数据
-- 成绩表 SC
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
对SC中的学生score进行整体排名
ROW_NUMBER
1 2 3 4 5 6 7 没有重复排名，依次递增
SET @i := 0;
SET @p := 0;
SET @q := 0;
SELECT t1.SId
,t1.CId
,t1.score
,@p := t1.score
,if(@p=@q,@i,@i := @i+1) as dense_rank
,@q :=@p
from (
SELECT SId
,CId
,score
from SC
order by score desc
) t1;
DENSE_RANK
1 2 3 3 3 4 5 6 7 有重复时并列排名，最终排名也是连续的
SET @i := 0;
SET @p := 0;
SET @q := 0;
SELECT t1.SId
,t1.CId
,t1.score
,@p := t1.score
,if(@p=@q,@i,@i := @i+1) as dense_rank
,@q :=@p
from (
SELECT SId
,CId
,score
from SC
order by score desc
) t1;
RANK
1 2 3 3 3 6 7 8 有重复时并列排名，最终排名不连续
SET @i := 0;
SET @j := 0;
SET @p := 0;
SET @q := 0;
SELECT t1.SId
,t1.CId
,t1.score
,@j := @j + 1
,@p := t1.score
,if(@p=@q,@i,@i := @j) as rank
,@q :=@p
from (
SELECT SId
,CId
,score
from SC
order by score desc
) t1;
进行分组排名
ROW_NUMBER
查询每⻔课程成绩最好的前三名 1 2 3 4 5 6 7 没有重复排名，依次递增
SET @i := 0;
SET @p := 0;
SET @q := 0;
SELECT tt1.SId
,tt2.SName
,tt1.CId
,tt1.score
,tt1.rn
from (
select t1.SId
,t1.CId
,t1.score
,@p := t1.CId
,if(@p=@q,@i := @i + 1,@i :=1) as rn
,@q := @p
from (
select SId
,CId
,score
from SC
order by CId,score DESC
) t1
) tt1 join Student tt2 on tt1.rn
DENSE_RANK
查询每⻔课程成绩最好的前三名 1 2 3 3 3 4 5 6 7 有重复时并列排名，最终排名也是连续的
SET @i := 0;
SET @p := 0;
SET @q := 0;
SET @j := 0;
SET @k := 0;
SELECT tt1.SId
,tt2.SName
,tt1.CId
,tt1.score
,tt1.rn
from (
select t1.SId
,t1.CId
,t1.score
,@p := t1.CId
,@j := t1.score
,if(@p=@q,if(@j=@k,@i,@i := @i + 1),@i :=1) as rn
,@q := @p
,@k := @j
from (
select SId
,CId
,score
from SC
order by CId,score DESC
) t1
) tt1 join Student tt2 on tt1.rn
RANK
查询每⻔课程成绩最好的前三名 1 2 3 3 3 6 7 8 有重复时并列排名，最终排名不连续
SET @i := 0;
SET @p := 0;
SET @q := 0;
SET @j := 0;
SET @k := 0;
SET @m := 1;
SELECT tt1.SId
,tt2.SName
,tt1.CId
,tt1.score
,tt1.rn
from (
select t1.SId
,t1.CId
,t1.score
,@p := t1.CId
,@j := t1.score
,if(@p=@q,@m := @m + 1,@m := 1)
,if(@p=@q,if(@j=@k,@i,@i := @m),@i :=1) as rn
,@q := @p
,@k := @j
from (
select SId
,CId
,score
from SC
order by CId,score DESC
) t1
) tt1 join Student tt2 on tt1.rn
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持IT俱乐部。
