三种常见的排名

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;
SELECT  t1.SId,t1.CId,t1.score,@i := @i + 1 as row_number
from (SELECT  SId,CId,scorefrom SCorder 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,scorefrom SCorder 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,scorefrom SCorder 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 := @pfrom (select  SId,CId,scorefrom SCorder by CId,score DESC) t1
) tt1 join Student tt2 on tt1.rn<=3 and tt1.SId = tt2.SId
order by tt1.CId,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 := @jfrom (select  SId,CId,scorefrom SCorder by CId,score DESC) t1
) tt1 join Student tt2 on tt1.rn<=3 and tt1.SId = tt2.SId
order by tt1.CId,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 := @jfrom (select  SId,CId,scorefrom SCorder by CId,score DESC) t1
) tt1 join Student tt2 on tt1.rn<=3 and tt1.SId = tt2.SId
order by tt1.CId,tt1.rn;