先设计一张表
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