ROWNUM如何在分页查询中工作？

Question

因此，我想在Oracle数据库中选择一系列行。我需要这样做是因为表中有数百万行，并且我想将结果分页给用户（如果您知道另一种在客户端执行此操作的方法，那么我正在使用JavaFX，但我没有认为通过网络发送所有数据以在客户端对它们进行分页是个好主意）。

我有以下查询：

Select * From (Select t.*, rownum r from PERSON t) Where r > 100 and r < 110;

该100和110只是例子。在应用程序中，我只是要求下限并添加10_000的大小以获取接下来的10_000行。

现在rownum列出现在结果中，我不想看到它。由于我对SQL不太熟悉，因此我有以下问题：

1. 为什么（这是我第一次尝试，直到我搜索SO）Select * From Person Where rownum > 100 and rownum < 110;返回0行？

2. 为什么没有简单的方法可以做类似的事情Select ... FROM ... WHERE rownum BETWEEN lowerBound AND upperBound？

3. 如何摆脱r结果值中的列？从那里SQL使用SELECT * columnA除外] FROM tableA排除一列？我显然需要创建一个视图或一个临时表，但是还有另一种考虑我的查询的方法吗？

4. 它能确保正确的分页吗？我阅读了本文的“用ROWNUM进行分页”一节，说我应该按唯一的顺序对值进行排序以获得一致的分页（因此rownum，如果可以确认的话，我认为按次排序就可以了）。它不会破坏使用的目的FIRST_ROWS(N)吗？

我希望不要太多，我可以分为几个问题，但是我认为将它们折叠起来是很重要的，因为它们关系密切。

Answer 1

您有4个问题，所有问题都围绕 ROWNUM 的用法和功能。我将一个接一个地回答每个问题。

为什么（这是我第一次尝试，直到我搜索SO为止）选择* From Person where rownum> 100 and rownum <110;
返回0行？

Thomas Kyte在这里很好地解释了ROWNUM和分页。

甲 ROWNUM 它通过后值被分配给行 谓词相
的查询，但查询之前是否有任何分选或聚集。另外，仅在分配ROWNUM值后才递增它，这就是以下查询永远不会返回行的原因：

select * 
  from t 
 where ROWNUM > 1;

因为第一行的ROWNUM> 1不正确，所以ROWNUM不会前进到2。因此，任何ROWNUM值都不会大于1。

为什么没有简单的方法可以执行Select … FROM … lowerBound和upperBound之间的rownum呢？

就在这里。从 Oracle 12c 开始，您可以使用新的 Top-n行限制
功能。

例如，以下查询将按升序返回员工的 最高薪水 到 第 4 高薪 至 第7高薪 ：

SQL> SELECT empno, sal
  2  FROM   emp
  3  ORDER BY sal
  4  OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

     EMPNO        SAL
---------- ----------
      7654       1250
      7934       1300
      7844       1500
      7499       1600

SQL>

如何摆脱结果值中的r列？

代替select *，在外部查询中列出所需的列名称。对于频繁使用的查询，创建视图是一项简单的一次性活动。

或者， SQL*Plus 您可以使用 NOPRINT 命令。它不会显示您不想显示的列名。但是，它仅在SQL * Plus中有效。

例如，

COLUMN column_name NOPRINT

例如，

SQL> desc dept
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 DEPTNO                                             NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

SQL> COLUMN dname NOPRINT
SQL> COLUMN LOC NOPRINT
SQL> SELECT * FROM dept;

    DEPTNO
----------
        10
        20
        30
        40

SQL>

它能确保正确的分页吗？

是的，如果您正确编写了分页查询。

例如，

SELECT val
FROM   (SELECT val, rownum AS rnum
        FROM   (SELECT val
                FROM   t
                ORDER BY val)
        WHERE rownum <= 8)
WHERE  rnum >= 5;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

SQL>

或者，如上所示，在12c上使用新的行限制功能。