总结几种MySQL中常见的排名问题

前言：

在某些应用场景中，我们经常会遇到一些排名的问题，比如按成绩或年龄排名。排名也有多种排名方式，如直接排名、分组排名，排名有间隔或排名无间隔等等，这篇文章将总结几种mysql中常见的排名问题。

创建测试表

create table scores_tb (
 id int auto_increment primary key,
 xuehao int not null, 
 score int not 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);

# 查看下插入的数据
mysql> select * from scores_tb;
+----+--------+-------+
| 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 |
+----+--------+-------+

1.普通排名

按分数高低直接排名，从1开始，往下排，类似于row number。下面我们给出查询语句及排名结果。

# 查询语句
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 |
+--------+-------+------+

上述查询语句中，我们申明了一个变量 @currank ,并将此变量初始化为0，查得一行将此变量加一，并以此作为排名。我们看到这类排名是没间隔的并且有些分数相同但排名不同。

2.分数相同，名次相同，排名无间隔

# 查询语句
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 |
+--------+-------+------+

3.并列排名，排名有间隔

另外一种排名方式是相同的值排名相同，相同值的下一个名次应该是跳跃整数值，即排名有间隔。

# 查询语句
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 |
+--------+-------+------+

上面介绍了三种排名方式，实现起来还是比较复杂的。好在mysql8.0增加了窗口函数，使用内置函数可以轻松实现上述排名。

mysql8.0 利用窗口函数实现排名

mysql8.0中可以利用 row_number()，dense_rank()，rank() 三个窗口函数实现上述三种排名，需要注意的一点是as后的别名，千万不要与前面的函数名重名，否则会报错，下面给出这三种函数实现排名的案例：

# 三条语句对于上面三种排名
select xuehao,score, row_number() over(order by score desc) as row_r from scores_tb;
select xuehao,score, dense_rank() over(order by score desc) as dense_r from scores_tb;
select xuehao,score, rank() over(order by score desc) as r from scores_tb;

# 一条语句也可以查询出不同排名
select xuehao,score,
 row_number() over w as 'row_r',
 dense_rank() over w as 'dense_r',
 rank()  over w as 'r'
from `scores_tb`
window w as (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 |
+--------+-------+-------+---------+---+

总结：

本文给出三种不同场景下实现统计排名的sql，可以根据不同业务需求选取合适的排名方案。对比mysql8.0，发现利用窗口函数可以更轻松实现排名，其实业务需求远远比我们举的示例要复杂许多，用sql实现此类业务需求还是需要慢慢积累的。

以上就是总结几种mysql中常见的排名问题的详细内容，更多关于mysql 排名的资料请关注其它相关文章！