介绍

下面3个函数 搭配 over (PARTITION by ... order by ...) 使用

• row_number()：为每行增加一个序号，同一分区中，序号从1开始，下一行增加1

• rank()：与 row_number() 相同，但是，当 order by值相同时，序号相同，但会占用下一行的序号

• dense_rank()：与 row_number() 相同，但是，当 order by值相同时，序号相同，不会 占用下一行的序号

应用场景

查询统计 TopN

如：

• 查询工资最高的前3名员工
• 销量最多的10件商品
• 查询成绩最高的前3名学生（并列第一，不占用第二、第三的名额）

例子

SQL

根据 depnot 分区，每个区内根据 sal 进行排序

select deptno,ename,sal,

row_number()  over( PARTITION by deptno order by sal desc) as row_num,
rank() over (PARTITION by deptno order by sal desc) as rnk,
dense_rank() over (PARTITION by deptno order by sal desc) as dns_rnk

from emp

执行结果如下：

解释：

根据 depnot 分区，每个区内根据 sal 进行排序

• row_number()：不论 sal 是多少，都逐行递增序号

• rank()：当 sal 相同时， 序号相同，但会占用下一行序号

• dense_rank()：当 sal 相同时， 序号相同，不会 占用下一行序号

要搭配 order by 使用

如果不加 order by ...，如下：

select deptno,ename,sal,

row_number()  over( PARTITION by deptno ) as row_num,
rank() over (PARTITION by deptno ) as rnk,
dense_rank() over (PARTITION by deptno ) as dns_rnk

from emp

执行如下图：

• row_num：分区内序号 不知道 是根据哪一列排序的
• rnk、dns_rnk：序号都是1，没有意义

案例

查询统计每个部门 工资 最高的 2名 员工的部门编号、姓名、工资（每个部门 只要前2名）

分析

根据 deptno 分区，根据 sal 倒序排序，由于 每个部门只要2名，所以取 row_number()

row_number() 实现

with tbl as (
    select deptno,ename,sal,
    row_number() over (PARTITION by deptno order by sal desc) as row_num
    from emp
)
select deptno,ename,sal from tbl where row_num<=2

执行结果：

注意： 部门编号是 20 的，前 2名 员工工资相同，使用 row_number() 仍排序 1、2

rank()实现

with tbl as (
    select deptno,ename,sal,rank() over( PARTITION by deptno order by sal desc) as rk  from emp 
)
select * from tbl where rk<=2

执行结果：

特点：使用 rank() 实现，可知道 deptno是 20的，SCOTT、FORD 是 并列第一，这是 row_number() 不具备的功能

案例2

查询统计每个部门 工资 最高的 2名 员工的部门编号、姓名、工资（工资相同的，不多占用名额）

类似名次，并列第一的，不会占用第二、第三的名额

分析

根据 deptno 分区，根据 sal 倒序排序，由于 工资相同的，不多占用名额，所以取 dense_rank()

SQL

with tbl as (
    select deptno,ename,sal,
    dense_rank() over (PARTITION by deptno order by sal desc) as dns_rnk
    from emp
)
select deptno,ename,sal from tbl where dns_rnk<=2

执行结果：

注意： 部门编号是 20 的，前 2名 员工工资相同，不占用名额，所以会查出 JONES

原文出处：/show_1IX2yCADntQE.html