三者的区别如下:
- rank()排序相同时会重复,总数不变,即会出现1、1、3这样的排序结果;
- dense_rank()排序相同时会重复,总数会减少,即会出现1、1、2这样的排序结果;
- row_number()排序相同时不会重复,会根据顺序排序。
具体实例
建表、插入数据
create table rownumber(
id varchar(10) not null,
name varchar(10) null,
age varchar(10) null,
salary int null
);
insert into rownumber(id,name,age,salary) values(1,'a',10,8000);
insert into rownumber(id,name,age,salary) values(1,'a2',11,7500);
insert into rownumber(id,name,age,salary) values(2,'b',12,7500);
insert into rownumber(id,name,age,salary) values(2,'b2',13,4500);
insert into rownumber(id,name,age,salary) values(3,'c',14,8000);
insert into rownumber(id,name,age,salary) values(3,'c2',15,20000);
insert into rownumber(id,name,age,salary) values(4,'d',16,30000);
insert into rownumber(id,name,age,salary) values(5,'d2',17,8000);
select * from rownumber;
数据
row_number()
select *, row_number() over(order by salary) as `rank` from rownumber;
row_number()排序结果
rank()
select *, rank() over(order by salary) as `rank` from rownumber;
rank()排序结果
dense_rank()
select *, dense_rank() over(order by salary) as `rank` from rownumber;
dense_rank()排序结果
row_number()的详细用法
1.分组排序
select *, row_number() over(partition by id order by salary) as `rank` from rownumber;
2. 每组内第二名的信息
select * from (
select *, row_number() over(partition by id order by salary) as `rank` from
rownumber) as temp
where `rank` = 2;
3. 给年龄在13岁到16岁的数据按salary排名
select *, row_number() over (order by salary) as `rank` from rownumber
where age between 13 and 16;
注:
row_number函数得到的列别名可用于order by 排序,因为order by执行在select之后。
where, group by, having都不可引用该列,因为这些语句执行在select之前,此时函数尚未计算出值。
--创建测试表
create table te.sc(id int, name varchar(20),class varchar(20), score int);
--给测试表插入数据
insert into te.sc values (1,'张飞','一年一班',100);
insert into te.sc values (2,'刘备','一年一班',99);
insert into te.sc values (3,'李逵','一年一班',95);
insert into te.sc values (4,'小动','一年一班',97);
insert into te.sc values (5,'小智','一年一班',80);
insert into te.sc values (6,'吕布','一年二班',67);
insert into te.sc values (7,'赵云','一年二班',90);
insert into te.sc values (8,'典韦','一年二班',89);
insert into te.sc values (9,'关羽','一年二班',70);
insert into te.sc values (10,'马超','一年二班',98);
insert into te.sc values (11,'张媛','一年一班',100);
--列出每个班分数排名前三的学生
select * from (select id, name, class, score ,
row_number() over (partition by class order by score desc) as r1,
rank() over (partition by class order by score desc) as r2 ,
dense_rank() over (partition by class order by score desc) as r3 from te.sc) B where r1<=3 ;
id name class score r1 r2 r3
1 张飞 一年一班 100 1 1 1
11 张媛 一年一班 100 2 1 1
2 刘备 一年一班 99 3 3 2
10 马超 一年二班 98 1 1 1
7 赵云 一年二班 90 2 2 2
8 典韦 一年二班 89 3 3 3
这三个函数的区别主要在分数一致的情况下,row_number()不重复排序,rank()重复且跳数字排序,dense_rank()重复且不跳数字排序。