##区别：

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 |
+----+----------+-------+------+------------+------------+

##转载

https://blog.csdn.net/weixin_43161811/article/details/112001469