row_number, rank(), dense_rank()的区别及具体用法示例(转自知乎)

链接：作者青墓

先上结论，三者的区别如下：

• 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;

rank()排序相同时会重复，总数不变，即会出现1、1、3这样的排序结果

select *, rank() over(order by salary) as `rank` from rownumber;

dense_rank()排序相同时会重复，总数会减少，即会出现1、1、2这样的排序结果；

select *, dense_rank() over(order by salary) as `rank` from rownumber;

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之前，此时函数尚未计算出值。