mysql排名的三种常见方式

乐一呵

三种常见的排名

row_number、dense_rank、rank在MySQL 5.7中的实现

准备数据


表结构说明

成绩表 SC(SId,CId,score)

• SId 学⽣编号
  
• CId 课程编号
  
• score 分数
  

创建SC表

1. create table SC(
   
2.     SId varchar(10),
   
3.     CId varchar(10),
   
4.     score decimal(18,1)
   
5. );

复制代码

插入数据

1. -- 成绩表 SC
   
2. insert into SC values('01' , '01' , 80);
   
3. insert into SC values('01' , '02' , 90);
   
4. insert into SC values('01' , '03' , 99);
   
5. insert into SC values('02' , '01' , 70);
   
6. insert into SC values('02' , '02' , 60);
   
7. insert into SC values('02' , '03' , 80);
   
8. insert into SC values('03' , '01' , 80);
   
9. insert into SC values('03' , '02' , 80);
   
10. insert into SC values('03' , '03' , 80);
    
11. insert into SC values('04' , '01' , 50);
    
12. insert into SC values('04' , '02' , 30);
    
13. insert into SC values('04' , '03' , 20);
    
14. insert into SC values('05' , '01' , 76);
    
15. insert into SC values('05' , '02' , 87);
    
16. insert into SC values('06' , '01' , 31);
    
17. insert into SC values('06' , '03' , 34);
    
18. insert into SC values('07' , '02' , 89);
    
19. insert into SC values('07' , '03' , 98);

复制代码

对SC中的学生score进行整体排名


ROW_NUMBER

1 2 3 4 5 6 7 没有重复排名，依次递增

1. SET @i := 0;
   
2. SET @p := 0;
   
3. SET @q := 0;
   
4. SELECT  t1.SId
   
5.         ,t1.CId
   
6.         ,t1.score
   
7.         ,@p := t1.score
   
8.         ,if(@p=@q,@i,@i := @i+1) as dense_rank
   
9.         ,@q :=@p
   
10. from (
    
11.     SELECT  SId
    
12.             ,CId
    
13.             ,score
    
14.     from SC
    
15.     order by score desc
    
16. ) t1;

复制代码

DENSE_RANK

1 2 3 3 3 4 5 6 7 有重复时并列排名，最终排名也是连续的

1. SET @i := 0;
   
2. SET @p := 0;
   
3. SET @q := 0;
   
4. SELECT  t1.SId
   
5.         ,t1.CId
   
6.         ,t1.score
   
7.         ,@p := t1.score
   
8.         ,if(@p=@q,@i,@i := @i+1) as dense_rank
   
9.         ,@q :=@p
   
10. from (
    
11.     SELECT  SId
    
12.             ,CId
    
13.             ,score
    
14.     from SC
    
15.     order by score desc
    
16. ) t1;

复制代码

RANK

1 2 3 3 3 6 7 8 有重复时并列排名，最终排名不连续

1. SET @i := 0;
   
2. SET @j := 0;
   
3. SET @p := 0;
   
4. SET @q := 0;
   
5. SELECT  t1.SId
   
6.         ,t1.CId
   
7.         ,t1.score
   
8.         ,@j := @j + 1
   
9.         ,@p := t1.score
   
10.         ,if(@p=@q,@i,@i := @j) as rank
    
11.         ,@q :=@p
    
12. from (
    
13.     SELECT  SId
    
14.             ,CId
    
15.             ,score
    
16.     from SC
    
17.     order by score desc
    
18. ) t1;

复制代码

进行分组排名
ROW_NUMBER

查询每⻔课程成绩最好的前三名 1 2 3 4 5 6 7 没有重复排名，依次递增

1. SET @i := 0;
   
2. SET @p := 0;
   
3. SET @q := 0;
   
4. SELECT  tt1.SId
   
5.         ,tt2.SName
   
6.         ,tt1.CId
   
7.         ,tt1.score
   
8.         ,tt1.rn
   
9. from (
   
10.     select  t1.SId
    
11.             ,t1.CId
    
12.             ,t1.score
    
13.             ,@p := t1.CId
    
14.             ,if(@p=@q,@i := @i + 1,@i :=1) as rn
    
15.             ,@q := @p
    
16.     from (
    
17.         select  SId
    
18.                 ,CId
    
19.                 ,score
    
20.         from SC
    
21.         order by CId,score DESC
    
22.     ) t1
    
23. ) tt1 join Student tt2 on tt1.rn<=3 and tt1.SId = tt2.SId
    
24. order by tt1.CId,tt1.rn;

复制代码

DENSE_RANK

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

1. SET @i := 0;
   
2. SET @p := 0;
   
3. SET @q := 0;
   
4. SET @j := 0;
   
5. SET @k := 0;
   
6. SELECT  tt1.SId
   
7.         ,tt2.SName
   
8.         ,tt1.CId
   
9.         ,tt1.score
   
10.         ,tt1.rn
    
11. from (
    
12.     select  t1.SId
    
13.             ,t1.CId
    
14.             ,t1.score
    
15.             ,@p := t1.CId
    
16.             ,@j := t1.score
    
17.             ,if(@p=@q,if(@j=@k,@i,@i := @i + 1),@i :=1) as rn
    
18.             ,@q := @p
    
19.             ,@k := @j
    
20.     from (
    
21.         select  SId
    
22.                 ,CId
    
23.                 ,score
    
24.         from SC
    
25.         order by CId,score DESC
    
26.     ) t1
    
27. ) tt1 join Student tt2 on tt1.rn<=3 and tt1.SId = tt2.SId
    
28. order by tt1.CId,tt1.rn;

复制代码

RANK

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

1. SET @i := 0;
   
2. SET @p := 0;
   
3. SET @q := 0;
   
4. SET @j := 0;
   
5. SET @k := 0;
   
6. SET @m := 1;
   
7. SELECT  tt1.SId
   
8.         ,tt2.SName
   
9.         ,tt1.CId
   
10.         ,tt1.score
    
11.         ,tt1.rn
    
12. from (
    
13.     select  t1.SId
    
14.             ,t1.CId
    
15.             ,t1.score
    
16.             ,@p := t1.CId
    
17.             ,@j := t1.score
    
18.             ,if(@p=@q,@m := @m + 1,@m := 1)
    
19.             ,if(@p=@q,if(@j=@k,@i,@i := @m),@i :=1) as rn
    
20.             ,@q := @p
    
21.             ,@k := @j
    
22.     from (
    
23.         select  SId
    
24.                 ,CId
    
25.                 ,score
    
26.         from SC
    
27.         order by CId,score DESC
    
28.     ) t1
    
29. ) tt1 join Student tt2 on tt1.rn<=3 and tt1.SId = tt2.SId
    
30. order by tt1.CId,tt1.rn;

复制代码

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持脚本之家。

来源:https://www.jb51.net/database/320523hnw.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！