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后的别名,千万不要与前面的函数名重名,否则会报错。
结果是很清楚的,具体就不解释了,相信大家看到结果都 很清楚三种排序的差别了。