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;
SELECT  t1.SId
        ,t1.CId
        ,t1.score
        ,@i := @i + 1 as row_number
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 @last_sco := 0;
SELECT  t1.SId
        ,t1.CId
        ,t1.score
        ,@last_sco
        ,if(t1.score = @last_sco,@i, @i := @i + 1) as dense_rank
        ,@last_sco := t1.score
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 @last_sco := 0;
SELECT  t1.SId
        ,t1.CId
        ,t1.score
        ,@last_sco
        ,@j := @j + 1
        ,if(t1.score = @last_sco,@i, @i := @j) as rank
        ,@last_sco := t1.score
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 @last_cid := '00';
SELECT  tt1.SId
        ,tt1.CId
        ,tt1.score
        ,tt1.rn
from (
	SELECT  t1.SId
            ,t1.CId
            ,@last_cid
            ,t1.score
            ,if(t1.CId = @last_cid,@i := @i + 1,@i := 1) as rn
            ,@last_cid := t1.CId
	from (
		SELECT  SId
                ,CId
                ,score
		from SC
		order by cid,score desc
	) t1
) tt1 where tt1.rn <=3;

DENSE_RANK

查询每⻔课程成绩最好的前三名 1 2 3 3 3 4 5 6 7 有重复时并列排名，最终排名也是连续的

SET @i := 0;
SET @last_sco := 0;
SET @last_cid := '00';
SELECT  t1.SId
        ,t1.CId
        ,t1.score
        ,@last_sco
        ,@last_cid
        ,if(t1.CId != @last_cid,@i := 1,if(t1.score = @last_sco,@i,@i:=@i+1)) as dense_rank
        ,@last_sco := t1.score
        ,@last_cid := t1.CId
from (
    SELECT  SId
            ,CId
            ,score
    from SC
    order by cid,score desc
) t1;

RANK

查询每⻔课程成绩最好的前三名 1 2 3 3 3 6 7 8 有重复时并列排名，最终排名不连续

SET @i := 0;
SET @j := 0;
SET @last_sco := 0;
SET @last_cid := '00';
SELECT  t1.SId
        ,t1.CId
        ,t1.score
        ,@last_sco
        ,@last_cid
        ,if(t1.Cid != @last_cid,@j := 1,@j := @j + 1)
        ,if(t1.CId != @last_cid,@i := 1,if(t1.score = @last_sco,@i,@i:=@j)) as rank
        ,@last_sco := t1.score
        ,@last_cid := t1.CId
from (
    SELECT  SId
            ,CId
            ,score
    from SC
    order by cid,score desc
) t1;