mysql排名的三種常見方式

更新時(shí)間：2024年05月01日 09:20:05 作者：創(chuàng)作者mateo

這篇文章主要介紹了mysql排名的三種常見方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

三種常見的排名

row_number、dense_rank、rank在MySQL 5.7中的實(shí)現(xiàn)

準(zhǔn)備數(shù)據(jù)

表結(jié)構(gòu)說明

成績(jī)表 SC(SId,CId,score)

SId 學(xué)?編號(hào)
CId 課程編號(hào)
score 分?jǐn)?shù)

創(chuàng)建SC表

create table SC(
    SId varchar(10),
    CId varchar(10),
    score decimal(18,1)
);

插入數(shù)據(jù)

-- 成績(jī)表 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);

對(duì)SC中的學(xué)生score進(jìn)行整體排名

ROW_NUMBER

1 2 3 4 5 6 7 沒有重復(fù)排名，依次遞增

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 有重復(fù)時(shí)并列排名，最終排名也是連續(xù)的

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 有重復(fù)時(shí)并列排名，最終排名不連續(xù)

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;

進(jìn)行分組排名

ROW_NUMBER

查詢每?課程成績(jī)最好的前三名 1 2 3 4 5 6 7 沒有重復(fù)排名，依次遞增

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

查詢每?課程成績(jī)最好的前三名 1 2 3 3 3 4 5 6 7 有重復(fù)時(shí)并列排名，最終排名也是連續(xù)的

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

查詢每?課程成績(jī)最好的前三名 1 2 3 3 3 6 7 8 有重復(fù)時(shí)并列排名，最終排名不連續(xù)

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;