Oracle 中 rownum、row_number()、rank()、dense_rank() 函数的用法

Ø  简介

在之前还以为在 Oracle 中只能使用 rownum 这个伪列来实现分页，其实不然。在 Oracle 也与 MSSQL 一样，同样支持 row_number 函数，以及和 rank、dense_rank 这两个函数。下面就来讨论 rownum 与 row_number 函数的区别，以及另外两个函数的使用。

1.   rownum 的使用

2.   rownum 使用需谨慎

3.   row_number() 函数

4.   rank() 与 dense_rank() 函数

5.   over() 函数结合聚合函数的使用

6.   综合案例

 

1.   rownum 的使用

rownum 是 Oracle 在查询时对结果集输出的一个伪列，这个列并不是真实存在的，当我们进行每一个 SELECT 查询时，Oracle 会帮我们自动生成这个序列号(rownum)，该序列号是顺序递增的，用于标识行号。通常可以借助 rownum 来实现分页，下面来看具体实现，比如我们需要取 emp 表中4到6行的记录：

1)   首先，我们来看一个奇怪的现象

SELECT * FROM emp WHERE rownum >= 4 AND rownum <= 6;

啪，一执行，呀，怎么没数据啊？这并不是我们写错了，要解释这个问题，我们先来看一个图，就明白其中原由了。

由图可以看出，当我们取出第一条记录时，此时(rownum = 1) >= 3不成立，所以该记录会被排除；然后再取第二条，此时任然 rownum = 1，因为只有成功满足一条记录，rownum 才会加1，所以不满足又被排除掉了。这样依次类推，最终都不满足条件，所以全部都被排除掉了。所以，以下语句始终查不出数据：

SELECT * FROM emp WHERE rownum > 1;

 

然后，在看另外一边（就是接下来用的这种判断方式），首先取第一条（满足），第二条也满足，直到(rownum = 7) <= 6，所以会取出6条记录，此时 rownum 的值为1,2,3,4,5,6。好了，搞清楚原理后我们就来实现。

 

2)   根据对 rownum 的分析，便改为以下语句

SELECT rownum, t1.* FROM (

  SELECT rownum rnum, t1.* FROM emp t1 WHERE rownum <= 6

) t1 WHERE t1.rnum >= 4;

这样，通过子查询，先取出前6行，再过滤掉前3行，就得到了我们需要的数据。注意：之前提过，每个 SELECT 都会产生一个 rownum 序列号，所有上面会可以输出两个 rownum 序列号，dual 也不例外：

SELECT t1.*, rownum FROM dual t1;

 

3)   除了使用以上语句，我们还可以这样写

SELECT rownum, t1.* FROM (

  SELECT rownum rnum, t1.* FROM emp t1

) t1 WHERE t1.rnum >= 4 AND rnum <= 6; --或使用 BETWEEN 子句

同样，可以完成以上功能。但分析一下，这种方式视乎没有上面的方式效率高，因为，这里是先查出所有（先将 rownum 分配好）数据，再进行第二次 rownum 过滤。

 

4)   有时候，我们还需要通过排序后再分页，该怎么实现呢？

使用排序并分页，也需要注意以下问题。

首先，我们来看下排序的全部数据：

SELECT * FROM emp ORDER BY sal;

按照上面的要求，我们应该是取出 empno(7521,7654,7934) 的员工，OK。

 

不是说用第一种方式，效率很高么？那就来使用它实现，更改的后的 SQL：

SELECT * FROM (

  SELECT rownum rnum, t1.* FROM emp t1 WHERE rownum <= 6 ORDER BY sal

) WHERE rnum >= 4;

结果是不是又纳闷了？怎么7698和7566也出来，而且还不是按我们预想的排序的！

 

好，我们再做个假设，以上语法是不是先查询出结果后，再将结果集过滤和排序的呢？为了验证这个疑点，很简单我们做以下测试：

SELECT * FROM (

  SELECT * FROM (SELECT rownum rnum, t1.* FROM emp t1)

  WHERE rownum <= 6 ORDER BY sal

) WHERE rnum >= 4;

结果与前面的推断是一样的，就是先查询出结果（产生的 rownum 是没有经过排序的），再排序，最后分页（过滤）。我们看一下未排序的原始数据：

SELECT rownum, t1.* FROM emp t1;

所以，我们得出一个结论：当我们同时过滤 rownum 和排序时，是先按默认的排序生成 rownum 后，再进行排序和过滤的。

 

5)   其实上面的排序和分页，并不是准确有效的。因为我们需要的是，rownum 的顺序是根据我们指定的排序产生的，这样再进行分页才是准确的。所以正确的排序和分页应该这样写：

SELECT * FROM (

  SELECT rownum rnum, t1.* FROM (SELECT * FROM emp ORDER BY sal) t1

) WHERE rnum BETWEEN 4 AND 6;

执行步骤：先根据指定的字段排序；再产生 rownum 序列号；最后进行分页。

 

2.   rownum 需谨慎

前面说到了，rownum 是先按默认的排序生成 rownum 后，再进行排序和过滤的，这一点很重要，不要搞错了。下面再来看个例子：

SELECT 'SQL1' AS SQL1, rownum AS rnum, "GoodsId", "CreateTime" FROM "G_Goods" t;

SELECT 'SQL2' AS SQL2, rownum AS rnum, "GoodsId", "CreateTime" FROM "G_Goods" t ORDER BY t."CreateTime" DESC;

SELECT 'SQL3' AS SQL3, rownum AS rnum, "GoodsId", "CreateTime" FROM "G_Goods" t WHERE rownum <= 10 ORDER BY t."CreateTime" DESC;

SELECT 'SQL4' AS SQL4, rownum AS rnum, t.* FROM (SELECT "GoodsId", "CreateTime" FROM "G_Goods" t ORDER BY t."CreateTime" DESC) t WHERE rownum <= 10;

SELECT 'SQL5' AS SQL5, t.* FROM (SELECT ROW_NUMBER() OVER(ORDER BY "CreateTime" DESC) AS rnum, "GoodsId", "CreateTime" FROM "G_Goods" t) t WHERE rnum <= 10;

1)   分析：

1.   首先看 SQL1 的默认排序结果，其实并不是按时间排序的；

2.   然后再看 SQL2，发现 rnum 并不是顺序递增的，而是根据时间排序显示的，最终取得数据应该是这些；

3.   好，再看 SQL3，是不是发现它其实取的是，默认排序后的前10条，然后再按时间倒序的（这是重点）；

4.   然后 SQL4，它是根据时间排序后，在输出结果集，在结果集中再取前10条，这样就对了。

5.   最后是 SQL5，当然也是对的，根据时间排序并同时生成行号。

 

2)   总结

rownum 作为条件判断时，一定不能同时与 ORDER BY 存在，切记！

 

3.   row_number() 函数

在前面使用 rownum 实现分页，虽然是可以实现的，但是看似是否有点别扭。因为当需要对分页排序时，rownum 总是先生成序列号再排序，其实这不时我们想要的。而 row_number() 函数则是先排序，再生成序列号。这也是 row_number 与 rownum 主要的区别。下面来看 row_number() 的使用：

n  语法：

row_number() over([partition by col1] order by col2 [ASC | DESC] [,col3 [ASC | DESC]]...)

参数解释：

row_number() over(): 是固定写法，即不能单独使用 row_nubmer() 函数；

partition by: 可选的。用于指定分组（或分开依据）的列，类似 SELECT 中的 group by 子句；

order by: 用于指定排序的列，类似 SELECT 中的 order by 子句。

 

1.   基本用法

SELECT row_number() over(order by empno) AS rnum, t1.* FROM emp t1;

 

2.   使用 row_number() 分页

SELECT * FROM (

  SELECT row_number() over(order by empno) AS rnum, t1.* FROM emp t1

) t WHERE t.rnum BETWEEN 4 AND 6;

是不是看上去，比使用 rownum 优雅了许多。

 

3.   使用 partition by 参数分区生成序号

当使用 partition by 参数时，序号将可能不是唯一的，因为序号的生成只会在当前分区中唯一，下一个分区又将从1开始计算，例如：

SELECT row_number() over(partition by deptno order by empno) AS rnum, t1.* FROM emp t1;

 

4.   rank() 与 dense_rank() 函数

rank() 与 row_number() 的区别在于，rank() 会按照排序值相同的为一个序号（以下称为：块），第二个不同排序值将显示所有行的递增值，而不是当前序号加1。看示例：

SELECT rank() over(order by job) rnum, job, ename FROM emp t1;

 

而 dense_rank() 函数，与 rank() 区别在于，第二个不同排序值，是对当前序号值加1，看示例：

SELECT dense_rank() over(order by job) rnum, job, ename FROM emp t1;

 

当指定 partition by 参数时，将根据指定的字段分组，进行分组计算序号值，序号值只在当前分组中有效，例如：

SELECT rank() over(partition by deptno order by job) rnum, job, ename, deptno FROM emp t1;

 

SELECT dense_rank() over(partition by deptno order by job) rnum, job, ename, deptno FROM emp t1;

 

5.   over() 函数结合聚合函数的使用

SELECT empno, ename, sal, hiredate, COUNT(sal) OVER(ORDER BY hiredate DESC) count FROM emp;

 

SELECT empno, ename, sal, hiredate, MAX(sal) OVER(ORDER BY hiredate ASC) max FROM emp;

 

SELECT empno, ename, sal, hiredate, MIN(sal) OVER(ORDER BY hiredate DESC) min FROM emp;

 

SELECT empno, ename, sal, hiredate, AVG(sal) OVER(ORDER BY hiredate DESC) avg FROM emp;

 

SELECT empno, ename, sal, hiredate, SUM(sal) OVER(ORDER BY hiredate DESC) sum FROM emp;

 

6.   综合案例

1)   查询前 100 条记录

SELECT * FROM emp WHERE rownum <= 100;

注意：如果以上语句需要排序后再筛选，并不是能使用 rownum 实现，而需要使用 row_number() 函数。

 

2)   查出 4 ~ 6 条的记录，并按员工编号排序（分页运用）

SELECT * FROM (SELECT row_number() over(order by empno) rnum, t.* FROM emp t) t

WHERE t.rnum >= 4 AND t.rnum <= 6;

 

3)   查出每个部门工资最高的员工

SELECT * FROM (SELECT row_number() over(partition by deptno order by sal DESC) rnum, t.* FROM emp t) t WHERE t.rnum = 1;

 

4)   查出每个部门工资最高的所有员工（排名并列的）

SELECT * FROM (SELECT rank() over(partition by deptno order by sal DESC) rnum, t.* FROM emp t) t WHERE t.rnum = 1;

 

5)   查出每个部门工资排名第三的所有员工（排名并列的）

SELECT * FROM (SELECT dense_rank() over(partition by deptno order by sal ASC) rnum, t.* FROM emp t) t WHERE t.rnum = 3;

 

注意：如果使用 rank() 是不行的，因为20号部门并列第二的员工有2个，序号3就被跳掉了，直接跳到了序号4，使用以下语句可以查看到：

SELECT rank() over(partition by deptno order by sal ASC) rnum, t.* FROM emp t;

所以，使用 rank() 将会得到错误的结果：

SELECT * FROM (SELECT rank() over(partition by deptno order by sal ASC) rnum, t.* FROM emp t) t WHERE t.rnum = 3;

 

Ø  总结

好了，关于排序函数就讨论到这里了，感觉有点难记住它们的区别。所以可以结合上面的案例去记忆：

1.   如果需要取前多少条记录，就使用 rownum 伪列。rownum 就类似于 SQL Server TOP 子句的用法，但是 rownum 不能用于排序并过滤的场合。

 

2.   如果取多少条到多少条的记录（分页），就是使用 row_number() 函数。

例如：查出 4 ~ 6 条的记录，并按员工编号排序。

 

3.   如果取某个组别中最大值记录或最小值的记录，也可以使用 row_number() 函数，并结合 partition by 参数。

例如：查出每个部门工资最高的员工。

 

4.   如果取某个组别中并列最大值或最小值得记录，就使用 rank() 函数，并结合 partition by 参数。

例如：查出每个部门工资最高的所有员工。

 

5.   如果取某个组别中并列排名几记录，就使用 dense_rank() 函数，并结合 partition by 参数。

例如：查出每个部门工资排名第三的所有员工。

 

当然，以上只是举例，还有更多的用法需要我们去举一反三。