mysql ROW_NUMBER、RANK、DENSE_RANK

一、简介

ROW_NUMBER、RANK、DENSE_RANK都是排名函数，在MySQL8.0以上版本中，已经支持这些函数，而8.0以下版本还未支持这些函数，这些函数到底有什么作用又有什么区别呢？我们通过SQL语句实现这些函数，并理解这些函数的作用和区别。

1. ROW_NUMBER是对数据进行排序，当出现相同数值时，排序序号增加；即不存在相同排名，排名序号连续。
2. RANK是跳跃排序，例如：一个第一名，两个第二名，那么接下来就是第四名；即存在相同排名，但排序序号不连续。
3. DENSE_RANK是连续的排序，两个第二名仍然跟着第三名；即存在相同排名，排名序号连续。

 

二、创建表、插入数据

root@localhost :test11:27:56>select * from jscore;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | 1    |    98 |
|  2 | 2    |    95 |
|  3 | 3    |    93 |
|  4 | 4    |    75 |
|  5 | 5    |    95 |
|  6 | 6    |    75 |
|  7 | 7    |    53 |
|  8 | 8    |    48 |
|  9 | 9    |    75 |
+----+------+-------+

 

三、代码实现 

1 ROW_NUMBER

ROW_NUMBER实现：

select id,name,score,@currank :=@currank+1 as rank
from jscore,(select @currank :=0)q
order by score desc,id desc;

root@localhost :test11:26:00>select row_number() over(order by score desc ) num,name,score from jscore;
+-----+------+-------+
| num | name | score |
+-----+------+-------+
|   1 | 1    |    98 |
|   2 | 2    |    95 |
|   3 | 5    |    95 |
|   4 | 3    |    93 |
|   5 | 4    |    75 |
|   6 | 6    |    75 |
|   7 | 9    |    75 |
|   8 | 7    |    53 |
|   9 | 8    |    48 |
+-----+------+-------+
9 rows in set (0.00 sec)

 

2、RANK实现：

select id,name,score,rank from
(select id,name,score,
@currank := if(@prerank=score,@currank,@incrank) as rank,
@incrank := @incrank+1,
@prerank := score
from jscore,(select @currank :=0,@prerank:=null,@incrank:=1)q
order by score desc,id desc)r;

 

root@localhost :test11:26:32>select rank() over(order by score desc ) num,name,score from jscore;
+-----+------+-------+
| num | name | score |
+-----+------+-------+
|   1 | 1    |    98 |
|   2 | 2    |    95 |
|   2 | 5    |    95 |
|   4 | 3    |    93 |
|   5 | 4    |    75 |
|   5 | 6    |    75 |
|   5 | 9    |    75 |
|   8 | 7    |    53 |
|   9 | 8    |    48 |
+-----+------+-------+
9 rows in set (0.00 sec)

 

3、

DENSE_RANK实现：

select id,name,score,rank from
(select id,name,score,
@currank := if(@prerank=score,@currank,@currank:=@currank+1) as rank,
@prerank:=score
from jscore,(select @currank :=0,@prerank:=null)q
order by score desc,id desc)r;

root@localhost :test11:27:22>select dense_rank() over(order by score desc ) num,name,score from jscore;
+-----+------+-------+
| num | name | score |
+-----+------+-------+
|   1 | 1    |    98 |
|   2 | 2    |    95 |
|   2 | 5    |    95 |
|   3 | 3    |    93 |
|   4 | 4    |    75 |
|   4 | 6    |    75 |
|   4 | 9    |    75 |
|   5 | 7    |    53 |
|   6 | 8    |    48 |
+-----+------+-------+
9 rows in set (0.00 sec)

 

参考：

https://blog.csdn.net/Jerry_Chang31/article/details/104481305?utm_medium=distribute.pc_aggpage_search_result.none-task-blog-2~aggregatepage~first_rank_v2~rank_v29-1-104481305.pc_aggpage_cache&utm_term=mysql%E5%BA%8F%E5%8F%B7%E5%87%BD%E6%95%B0&spm=1000.2123.3001.4430

https://blog.csdn.net/zhuzicc/article/details/106288246