MySQL的排序函数 rank()、row_number()、dense_rank()

先设计一张表

create table students(id int(4)  auto_increment primary key,name varchar(50) not null, score int(4) not null);

插入一些数据

insert into students(name,score) values('curry', 100),('klay', 99),('KD', 100), ('green', 90), ('James', 99), ('AD', 96);

查看一下插入的数据

select * from students;

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

select id, name, rank() over(order by score desc) as r from students;
select id, name, DENSE_RANK() OVER(order by score desc) as dense_r from students;
select id, name, row_number() OVER(order by score desc) as row_r from students;

OR （上面是一条一条的写，下面是合并写）

select id, name, rank() over(order by score desc) as r,DENSE_RANK() OVER(order by score desc) as dense_r,row_number() OVER(order by score desc) as row_r
from students;

结果显而易见。
文章转载于-https://blog.csdn.net/sqsltr/article/details/94408487