前言：
在某些应用场景中，我们经常会遇到一些排名的问题，比如按成绩或年龄排名。排名也有多种排名方式，如直接排名、分组排名，排名有间隔或排名无间隔等等，这篇文章将总结几种MySQL中常见的排名问题。
创建测试表
<code>create table scores_tb ( id int auto_increment primary key, xuehao int
not
null, score intnot
null ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into scores_tb (xuehao,score) values (1001
,89
),(1002
,99
),(1003
,96
),(1004
,96
),(1005
,92
),(1006
,90
),(1007
,90
),(1008
,94
| id |
xuehao| score |
+----+--------+-------+| 1 |
1001
| 89 |
| 2 |
1002
| 99 |
| 3 |
1003
| 96 |
| 4 |
1004
| 96 |
| 5 |
1005
| 92 |
| 6 |
1006
| 90 |
| 7 |
1007
| 90 |
| 8 |
1008
| 94 |
+----+--------+-------+/<code>1.普通排名
按分数高低直接排名，从1开始，往下排，类似于row number。下面我们给出查询语句及排名结果。
<code> SELECT xuehao, score, @curRank
:
= @curRank +1
AS rank FROM scores_tb, ( SELECT @curRank:
=0
) r ORDER BY score desc; +--------+-------+------+| xuehao |
score| rank |
+--------+-------+------+| 1002 |
99
| 1 |
| 1003 |
96
| 2 |
| 1004 |
96
| 3 |
| 1008 |
94
| 4 |
| 1005 |
92
| 5 |
| 1006 |
90
| 6 |
| 1007 |
90
| 7 |
| 1001 |
89
| 8 |
+--------+-------+------+/<code>上述查询语句中，我们申明了一个变量 @curRank ,并将此变量初始化为0，查得一行将此变量加一，并以此作为排名。我们看到这类排名是没间隔的并且有些分数相同但排名不同。
2.分数相同，名次相同，排名无间隔
<code># 查询语句
SELECT
xuehao
,score
,CASE
WHEN
@prevRank
= score THEN@curRank
WHEN
@prevRank
:= score THEN@curRank
:=@curRank
+1
END AS rank FROM scores_tb, (SELECT@curRank
:=0
,@prevRank
:= NULL) r ORDER BY score desc; # 排名结果 +--------
+-------
+------
+ |xuehao
|score
|rank
| +--------
+-------
+------
+ |1002
|99
|1
| |1003
|96
|2
| |1004
|96
|2
| |1008
|94
|3
| |1005
|92
|4
| |1006
|90
|5
| |1007
|90
|5
| |1001
|89
|6
| +--------
+-------
+------
+/<code>3.并列排名，排名有间隔
另外一种排名方式是相同的值排名相同，相同值的下一个名次应该是跳跃整数值，即排名有间隔。
<code># 查询语句
SELECT
xuehao
,score
,rank
FROM
(SELECT xuehao, score,@curRank
:= IF(@prevRank
= score,@curRank
,@incRank
) AS rank,@incRank
:=@incRank
+1
,@prevRank
:= score FROM scores_tb, ( SELECT@curRank
:=0
,@prevRank
:= NULL,@incRank
:=1
) r ORDER BY score desc)s
; # 排名结果 +--------
+-------
+------
+ |xuehao
|score
|rank
| +--------
+-------
+------
+ |1002
|99
|1
| |1003
|96
|2
| |1004
|96
|2
| |1008
|94
|4
| |1005
|92
|5
| |1006
|90
|6
| |1007
|90
|6
| |1001
|89
|8
| +--------
+-------
+------
+/<code>上面介绍了三种排名方式，实现起来还是比较复杂的。好在MySQL8.0增加了窗口函数，使用内置函数可以轻松实现上述排名。
MySQL8.0 利用窗口函数实现排名
MySQL8.0中可以利用 ROW_NUMBER()，DENSE_RANK()，RANK() 三个窗口函数实现上述三种排名，需要注意的一点是as后的别名，千万不要与前面的函数名重名，否则会报错，下面给出这三种函数实现排名的案例：
<code>
select
xuehao,score, ROW_NUMBER()OVER
(order
by
scoredesc
)as
row_rfrom
scores_tb;select
xuehao,score,DENSE_RANK
()OVER
(order
by
scoredesc
)as
dense_rfrom
scores_tb;select
xuehao,score,RANK
()over
(order
by
scoredesc
)as
rfrom
scores_tb;SELECT
xuehao,score, ROW_NUMBER()OVER
wAS
'row_r'
,DENSE_RANK
()OVER
wAS
'dense_r'
,RANK
()OVER
wAS
'r'
FROM
`scores_tb`
WINDOW
wAS
(ORDER
BY
`score`
desc
); + | xuehao | score | row_r | dense_r | r | + | 1002 | 99 | 1 | 1 | 1 | | 1003 | 96 | 2 | 2 | 2 | | 1004 | 96 | 3 | 2 | 2 | | 1008 | 94 | 4 | 3 | 4 | | 1005 | 92 | 5 | 4 | 5 | | 1006 | 90 | 6 | 5 | 6 | | 1007 | 90 | 7 | 5 | 6 | | 1001 | 89 | 8 | 6 | 8 | +/<code>总结：
本文给出三种不同场景下实现统计排名的SQL，可以根据不同业务需求选取合适的排名方案。对比MySQL8.0，发现利用窗口函数可以更轻松实现排名，其实业务需求远远比我们举的示例要复杂许多，用SQL实现此类业务需求还是需要慢慢积累的。
参考：
https://www.cnblogs.com/caicaizi/p/9803013.htmlhttps://blog.csdn.net/sqsltr/article/details/94408487