oracle 使用rownum的三种分页方式

下面来详细讲解“Oracle 使用 ROWNUM 的三种分页方式”的完整攻略。

什么是 ROWNUM

ROWNUM是Oracle中一个伪列（伪列是一个看起来像是表中某个字段、但实际上不是字段、由oracle为其预先设置了值的列），表示被检索出来的数据行的序号，起始值为1，每次增加1。

ROWNUM分页

在Oracle中使用ROWNUM进行分页，要注意结果集是先生成再排序，而ROWNUM值是在排序之后才生成。所以在使用ROWNUM分页时，它是不能直接根据数据行的序号进行翻页的，需要一定技巧。

目前常用的基于ROWNUM进行分页的方式有以下三种：

第一种方式：子查询

子查询方式是通过先进行查询操作，再在结果集上进行ROWNUM的限制，从而得到分页结果。其SQL写法如下：

SELECT 
    * 
FROM 
    (SELECT 
        ROWNUM rn, t.* 
     FROM 
        (SELECT 
            *
         FROM 
            表名
         ORDER BY 
            字段名 ASC/DESC) t
    ) tt 
WHERE 
    tt.rn > (当前页数-1)*每页显示的记录数 
AND 
    tt.rn <= 当前页数*每页显示的记录数;

示例：

假设表名为userinfo，包含id、username、age三个字段，现在需要查看第2页，每页显示5条数据，按照id升序排列，如下所示：

SELECT 
    * 
FROM 
    (SELECT 
        ROWNUM rn, t.* 
     FROM 
        (SELECT 
            *
         FROM 
            userinfo
         ORDER BY 
            id ASC) t
    ) tt 
WHERE 
    tt.rn > 5 
AND 
    tt.rn <= 10;

第二种方式：Oracle 12c 中的 FETCH FIRST

Oracle 12c中新增了一个语法，即 FETCH FIRST n ROWS ONLY，可以直接按照行数限制返回结果集的行数，非常便利。但需要注意的是，这个语法在其他数据库中不一定适用。

其SQL写法如下：

SELECT 
    *
FROM 
    表名 
ORDER BY 
    字段名 ASC/DESC 
FETCH FIRST n ROWS ONLY;

示例：

假设表名为userinfo，包含id、username、age三个字段，现在需要查看第2页，每页显示5条数据，按照id升序排列，如下所示：

SELECT 
    *
FROM 
    userinfo
ORDER BY 
    id ASC 
FETCH FIRST 5 ROWS ONLY 
OFFSET 5 ROW;

第三种方式：ROW_NUMBER()函数

ROW_NUMBER()函数是Oracle中的一个内置函数，可生成一个一组结果集中的行号，支持一些复杂的分页查询。

其SQL写法如下（注意必须使用分号进行多条SQL分隔）：

WITH 
    t AS 
    (
        SELECT 
            *
            ,ROW_NUMBER() OVER (ORDER BY 字段名) AS row_num
        FROM 
            表名
    )
SELECT 
    * 
FROM 
    t 
WHERE 
    row_num BETWEEN m AND n;

示例：

假设表名为userinfo，包含id、username、age三个字段，现在需要查看第2页，每页显示5条数据，按照id升序排列，如下所示：

WITH 
    t AS 
    (
        SELECT 
            *
            ,ROW_NUMBER() OVER (ORDER BY id) AS row_num
        FROM 
            userinfo
    )
SELECT 
    * 
FROM 
    t 
WHERE 
    row_num BETWEEN 6 AND 10;

这就是使用ROWNUM进行分页的三种方式。希望对您有帮助。