准备一个环境
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;
写在一个表中观看效果:
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;
需要注意的一点是as后的别名,千万不要与前面的函数名重名,否则会报错。
由结果可以看出:
- rank(),根据条件排序,相同条件值排序相同,都占用一个位置
- DENSE_RANK(),根据条件排序,相同条件值排序相同,同一个值占用一个位置
- row_number(),根据条件有序排列,每条数据占用一个位置