Oracle语法：ROWNUM的使用和分页查询

发表于 | 分类于 ， |
| 字数统计: 731

概述

ROWNUM是Oracle中引入的一种虚列，也称作伪列。在物理上这个虚列没有存储在表中，只是在查询时才构造出来。

特点

关于ROWNUM有以下主要特点：

  1. ROWNUM不属于任何表。它存在的前提，是先有结果表
  2. ROWNUM总是从1开始，且值是连续的整数。
  3. ROWNUM一般只和<、<=、!=、=1一起使用。
  4. 使用ROWNUM进行分页查询需要把ROWNUM转化为实列，并针对ROWNUM查询。

机制原理

ROWNUM不属于任何表，比如下面这个sql执行时会报错的。

1
SELECT t.username, t.ROWNUM FROM T_USER t;

而应该是

1
SELECT t.username, ROWNUM FROM T_USER t;

因为ROWNUM是对结果集加的一个伪列，即先查出结果集给查询出的结果标上序号(强调：先要有结果集)，序号从1开始，连续递增，不存在序号跳跃的现象。例如

1
SELECT ROWNUM, t.name FROM tableA t;

结果为：

ROWNUM name
1 gulu
2 gaolaozhuang
3 daideng
4 qinqiang

如果加上限制条件：

1
SELECT ROWNUM, t.name FROM tableA t WHERE t.name NOT LIKE 'd%';

结果就变成了：

ROWNUM name
1 gulu
2 gaolaozhuang
3 qinqiang

ROWNUM支持能够查出正确记录的操作符为<、<=、!=、=1，对于带有>、>=、=N(N>1)、BETWEEN…AND…这些筛选条件的SQL，虽然执行不报错，但是查不出我们期望的结果。

至于为什么会这样，其原因还是上面所说的，即：ROWNUM是对查询出来的结果集加上的一个伪列。当我们执行下面这条SQL，看看执行时发生了什么。

1
SELECT ROWNUM, t.name FROM tableA t WHERE ROWNUM > 3;

上面已经说道ROWNUM总是从1开始，所以上面这个SQL执行完 tableA 的查询后，在结果集上加上了ROWNUM列，第一行的值为1，然后执行过滤条件 ROWNUM>3，因为第一条记录不满足条件，剔除。这个时候新的结果集产生了，原来的第二条记录就成了第一条，但变完之后 第一行的ROWNUM还是1，这条记录还是会被剔除，以此类推，最后查出来的数据是空的。

分页查询

上面说ROWNUM一般不和>、>=、=N(N>1)、BETWEEN…AND…这条筛选条件使用，但如果要查询5<ROWNUM<10的数据要怎么查呢？

可以通过子查询来解决，这样可以把序列转化为临时表的实列，这其实就是Oracle里面分页查询的方式。如下是几条简单的分页查询示例。

  1. 查询前10条记录

    1
    　　SELECT * FROM TestTable WHERE ROWNUM <= 10;

  2. 查询第11到第20条记录

    1
    　　SELECT * FROM (SELECT TestTable.*, ROWNUM ro FROM TestTable WHERE ROWNUM <=20) WHERE ro > 10;

  3. 按照name字段升序排列后的前10条记录

    1
    　　SELECT * FROM (SELECT * FROM TestTable ORDERY BY name ASC) WHERE ROWNUM <= 10;

  4. 按照name字段升序排列后的第11到第20条记录

    1
    　　SELECT * FROM (SELECT tt.*, ROWNUM ro FROM (SELECT * FROM TestTable ORDER BY name ASC) tt WHERE ROWNUM <=20) WHERE ro > 10;
------ 本文完 ------