mysql自动排序函数dense_rank() over()、rank() over()、row_num() over()用法和区别

区别：

• rank()：是并列排序，会跳过重复序号
• dense_rank()：是并列排序，不会跳过重复序号
• row_number()：是顺序排序，不跳过任何一个序号，就是行号

用法：

数据准备：

create table students(
	id int(11)  auto_increment primary key,
	name varchar(50) not null, 
	score int(4) not null
	);
 
insert into students(name,score) values
('zhangsan', 100),
('lisi', 99),
('wangwu', 100), 
('trx', 90), 
('pjf', 99), 
('wzm', 96);

查看下插入的数据：

mysql> select * from students;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan |   100 |
|  2 | lisi     |    99 |
|  3 | wangwu   |   100 |
|  4 | trx      |    90 |
|  5 | pjf      |    99 |
|  6 | wzm      |    96 |
+----+----------+-------+

使用三种不同的方法进行排序：

select 
    id, 
    name, 
    rank() over(order by score desc) `rank`,
    row_number() over(order by score desc) `row_number`,
    dense_rank() over(order by score desc) `dense_rank`
from students;
 
--------------------------------- 结果 ------------------------------------
+----+----------+-------+------+------------+------------+
| id | name     | score | rank | row_number | dense_rank |
+----+----------+-------+------+------------+------------+
|  1 | zhangsan |   100 |    1 |          1 |          1 |
|  3 | wangwu   |   100 |    1 |          2 |          1 |
|  2 | lisi     |    99 |    3 |          3 |          2 |
|  5 | pjf      |    99 |    3 |          4 |          2 |
|  6 | wzm      |    96 |    5 |          5 |          3 |
|  4 | trx      |    90 |    6 |          6 |          4 |
+----+----------+-------+------+------------+------------+

 

那么，如何用一般的SQL实现这几个函数的效果呢？

(1) rank()是并列排序，会跳过重复序号

思路：外查询按分数降序排列，子查询查出大于当前成绩的个数+1,个数+就是排名

select 
	id, 
	name, 
	a.score,
	(select count(score)+1 from students where score > a.score)  `rank`
from students a
order by score desc;
--------------------------------- 结果 ------------------------------------
+----+----------+-------+------+
| id | name     | score | rank |
+----+----------+-------+------+
|  1 | zhangsan |   100 |    1 |
|  3 | wangwu   |   100 |    1 |
|  2 | lisi     |    99 |    3 |
|  5 | pjf      |    99 |    3 |
|  6 | wzm      |    96 |    5 |
|  4 | trx      |    90 |    6 |
+----+----------+-------+------+

(2) dense_rank() 是并列排序，不会跳过重复序号

思路：用外查询按分数降序排列，子查询查出大于等于当前成绩去重后的个数，这个个数就是当前分数的名次。

select  
	id,  
	name, 
	a.score, 
	(select count(distinct score) from students where score >= a.score) as dense_rank 
from students a
order by score desc;
--------------------------------- 结果 ------------------------------------
+----+----------+-------+------------+
| id | name     | score | dense_rank |
+----+----------+-------+------------+
|  1 | zhangsan |   100 |          1 |
|  3 | wangwu   |   100 |          1 |
|  2 | lisi     |    99 |          2 |
|  5 | pjf      |    99 |          2 |
|  6 | wzm      |    96 |          3 |
|  4 | trx      |    90 |          4 |
+----+----------+-------+------------+

(3) row_number() 是顺序排序，不跳过任何一个序号，其实就是排序后的行号

思路：这种方式排序的关键在于如何获取行号，行号既是排名的名次

先看结论：

select  
	id,  
	name, 
	a.score, 
	(@rowNum:=@rowNum+1) as row_number 
from students a,(select @rowNum:=0) b 
order by score desc;
--------------------------------- 结果 ------------------------------------
+----+----------+-------+------------+
| id | name     | score | row_number |
+----+----------+-------+------------+
|  1 | zhangsan |   100 |          1 |
|  3 | wangwu   |   100 |          2 |
|  2 | lisi     |    99 |          3 |
|  5 | pjf      |    99 |          4 |
|  6 | wzm      |    96 |          5 |
|  4 | trx      |    90 |          6 |
+----+----------+-------+------------+

那么，如何获取行号？关键：自定义变量@rowNum:=0;

假设有这样的数据

mysql> select id,name from students;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
|  4 | trx      |
|  5 | pjf      |
|  6 | wzm      |
+----+----------+

再看

mysql> SELECT @rowNum:=0;
+------------+
| @rowNum:=0 |
+------------+
|          0 |
+------------+
 
mysql> SELECT @rowNum:=1;
+------------+
| @rowNum:=1 |
+------------+
|          1 |
+------------+

SELECT @rowNum:=0; 表示声明了一个叫rowNum的变量并赋值为0，这个变量名是自定义的，在sql中我们可以对这个变量进行计算，如：

mysql> select (@rowNum:=@rowNum+1) as num from (SELECT @rowNum:=0) a;
+------+
| num  |
+------+
|    1 |
+------+
 
mysql> select (@rowNum:=@rowNum+5) as num from (SELECT @rowNum:=0) a;
+------+
| num  |
+------+
|    5 |
+------+

此时 (SELECT @rowNum:=0) a 相当于一个临时表，如果将它与students表进行连接将会得到：

mysql> select b.*,id,name from students a,(SELECT @rowNum:=0) b;
+------------+----+----------+
| @rowNum:=0 | id | name     |
+------------+----+----------+
|          0 |  1 | zhangsan |
|          0 |  2 | lisi     |
|          0 |  3 | wangwu   |
|          0 |  4 | trx      |
|          0 |  5 | pjf      |
|          0 |  6 | wzm      |
+------------+----+----------+

可以看到，b表只有一条数据，与a表连接后就有了a表的6条数据，如果我们对自定义变量@rowNum:进行+1操作，会有什么效果呢？

mysql> select (@rowNum:=@rowNum+1) as num,id,name from students a,(SELECT @rowNum:=0) b;
+------+----+----------+
| num  | id | name     |
+------+----+----------+
|    1 |  1 | zhangsan |
|    2 |  2 | lisi     |
|    3 |  3 | wangwu   |
|    4 |  4 | trx      |
|    5 |  5 | pjf      |
|    6 |  6 | wzm      |
+------+----+----------+
6 rows in set (0.00 sec)

这就达到了查询结果带行号的目的，我们推演一下两个表连接的过程：

1. 一开始自定义变量@rowNum:=0，a表第一条数据(1,'zhangsan')与自定义变量join，不过我们取的是@rowNum:=@rowNum+1，即0+1，那么最终就会得到（1,1,'zhangsan'）
2. 此时自定义变量@rowNum:=1，a表第一条数据(2,'lisi')与自定义变量join,即1+1，那么最终得到(2,2,'lisi')
3. ....(以此类推)

由此，我们就能得到sql查询带行号的结果。