mysql排名的三种常见方式_Mysql

三种常见的排名

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;