oracle分页sql

A. Oracle常见sql分页实现方案

在Oracle中 用SQL来实现分页有很多种实现方式 但有些语句可能并不是很通用 只能用在一些特殊场景之中

以下介绍三种比较通用的实现方案 在以下各种实现中 ROWNUM是一个最核心的关键词 在查询时他是一个虚拟的列 取值为 到记录总数的序号

首先来介绍我们工作中最常使用的一种实现方式

SELECT *

FROM (SELECT ROW_ * ROWNUM ROWNUM_

FROM (SELECT *

FROM TABLE

WHERE TABLE _ID = XX

ORDER BY GMT_CREATE DESC) ROW_

WHERE ROWNUM <= )

WHERE ROWNUM_ >= ;

其中最内层的查询SELECT为不进行翻页的原始查询语句 可以用自己的任意Select SQL替换 ROWNUM <= 和ROWNUM >= 控制分页查询的每页的范围

分页的目的就是控制输出结果集大小 将结果尽快的返回 上面的SQL语句在大多数情况拥有较高的效率 主要体现在WHERE ROWNUM <= 这句上 这样就控制了查询过程中的最大记录数

上面例子中展示的在查询的第二层通过ROWNUM <= 来控制最大值 在查询的最外层控制最小值 而另一种方式是去掉查询第二层的WHERE ROWNUM <= 语句 在查询的最外层控制分页的最小值和最大值 此时SQL语句如下 也就是要介绍的第二种实现方式

SELECT *

FROM (SELECT A * ROWNUM RN

FROM (SELECT *

FROM TABLE

WHERE TABLE _ID = XX

ORDER BY GMT_CREATE DESC) A)

WHERE RN BEEEN AND ;

由于Oracle可以将外层的查询条件推到内层查询中 以提高内层查询的执行效率 但不能跨越多层

对于第一个查询语句 第二层的查询条件WHERE ROWNUM <= 就可以被Oracle推入到内层查询中 这样Oracle查询的结果一旦超过了ROWNUM限制条件 就终止查询将结果返回了

而 第二个查询语句 由于查询条件BEEEN AND 是存在于查询的第三层 而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义 因为最内层查询不知道RN代表什么） 因此 对于第二个查询语句 Oracle最内层返回给中间层的是所有满足条件的数据 而中间层返回给最外层的也是所有数据 数据的过滤在最外层完成 显然这个效率要比第一个查询低得多

以上两种方案完全是通过ROWNUM来完成 下面一种则采用ROWID和ROWNUM相结合的方式 SQL语句如下

SELECT *

FROM (SELECT RID

FROM (SELECT R RID ROWNUM LINENUM

FROM (SELECT ROWID RID

FROM TABLE

WHERE TABLE _ID = XX

ORDER BY GMT_CREATE DESC) R

WHERE ROWNUM <= )

WHERE LINENUM >= ) T

TABLE T

WHERE T RID = T ROWID;

从语句上看 共有 层Select嵌套查询 最内层为可替换的不分页原始SQL语句 但是他查询的字段只有ROWID 而没有任何待查询的实际表字段 具体查询实际字段值是在最外层实现的

这种方式的原理大致为 首先通过ROWNUM查询到分页之后的 条实际返回记录的ROWID 最后通过ROWID将最终返回字段值查询出来并返回

和前面两种实现方式相比 该SQL的实现方式更加繁琐 通用性也不是非常好 因为要将原始的查询语句分成两部分（查询字段在最外层 表及其查询条件在最内层）

但这种实现在特定场景下还是有优势的 比如我们经常要翻页到很后面 比如 条记录中我们经常需要查 及其以后的数据 此时该方案效率可能要比前面的高

因为前面的方案中是通过ROWNUM <= 来控制的 这样就需要查询出 条数据 然后取最后 之间的数据 而这个方案直接通过ROWID取需要的那 条数据

从不断向后翻页这个角度来看 第一种实现方案的成本会越来越高 基本上是线性增长 而第三种方案的成本则不会像前者那样快速 他的增长只体现在通过查询条件读取ROWID的部分

当然 除了以上提了这些方案 我们还可以用以下的SQL来实现

SELECT *

FROM TABLE

WHERE TABLE _ID NOT IN

(SELECT TABLE _ID FROM TABLE WHERE ROWNUM <= )

AND ROWNUM <= ;

SELECT *

FROM TABLE

WHERE ROWNUM <=

MINUS

SELECT * FROM TABLE WHERE ROWNUM <= ;

………………

注意 当ROWNUM作为查询条件时 他是在order by之前执行 所以要特别小心

比如我们想查询TABLE 中按TABLE _ID倒序排列的前 条记录不能用如下的SQL来完成

B. 在oracle数据库中的分页SQL语句怎么写

前提：

分页参数：size = 20 page = 2；

没有order by的查询；

嵌套子查询，两次筛选（推荐使用）。

SQL语句：

SELECT *

FROM (SELECT ROWNUM AS rowno, t.*

FROM DONORINFO t

WHERE t.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd')

AND TO_DATE ('20060731', 'yyyymmdd')

AND ROWNUM <= 20*2) table_alias

WHERE table_alias.rowno > 20*(2-1);

(2)oracle分页sql扩展阅读：

rownum总是从1开始的，第一条不满足去掉的话，第二条的rownum 又成了1。依此类推，所以永远没有不满足条件的记录。

可以这样理解：rownum是一个序列，是Oracle数据库从数据文件或缓冲区中读取数据的顺序。它取得第一条记录则rownum值为1，第二条为2。

依次类推：当使用“>、>=、=、between...and”这些条件时，从缓冲区或数据文件中得到的第一条记录的rownum为1，不符合sql语句的条件，会被删除，接着取下条。下条的rownum还会是1，又被删除，依次类推，便没有了数据。

C. oracle rownum分页

在Oracle数据库中，rownum是一个预处理字段，它默认从1开始标注记录顺序。只有当记录集已经满足指定条件后，才会进行后续的编号。当使用rownum进行分页时，如果条件是rownum >= 6，那么第一条记录的rownum为1，显然不满足条件。Oracle会将第二条记录的rownum标注为1，继续进行比较，但同样不满足条件。这个过程会不断循环，直到找到满足条件的记录。

为了正确地使用rownum进行分页，可以采用子查询的方式。例如，要获取从第6条开始的前10条记录，可以使用以下SQL语句：

select * from ( select a1.*, rownum rwn from emp a1 where rownum = 6;

或者使用row_number()函数进行排序：

select * from ( select qx.*,row_number() over(order by qx.empno) rwn from emp qx ) where rwn between 6 and 10;

这两种方法都能确保从第6条记录开始，获取符合条件的前10条记录。

使用row_number()函数的好处在于它可以根据指定的排序规则对记录进行编号，而rownum的编号是基于查询条件的匹配结果进行的，可能会导致分页结果不符合预期。因此，在进行分页操作时，推荐使用row_number()函数。

需要注意的是，上述SQL语句中的emp表是一个示例表名，实际使用时需要替换为具体的表名。同时，row_number()函数的排序规则可以根据实际需求进行调整。

综上所述，正确使用rownum和row_number()函数，可以有效地进行Oracle数据库中的分页操作，确保分页结果的准确性。