mysql排名的三种常见方式总结_F11 - 专业站长和开发者的学习网站

三种常见的排名

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<=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 := @j

from (

select SId

,CId

,score

from SC

order 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 := @j

from (

select SId

,CId

,score

from SC

order by CId,score DESC

) t1

) tt1 join Student tt2 on tt1.rn<=3 and tt1.SId = tt2.SId

order by tt1.CId,tt1.rn;