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

Question

所以我想在 Oracle DB 中选择一系列行。我需要这样做，因为我在表中有数百万行，我想将结果分页给用户(如果您知道在客户端执行此操作的另一种方法，如果重要，我正在使用 JavaFX，但我不认为通过网络发送所有数据以在客户端对它们进行分页是个好主意)。
所以看完这篇文章后:SQL ROWNUM how to return rows between a specific range ，我有以下查询:

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

100和 110只是例子。在应用程序中，我只要求下限并添加 10_000 的大小以获取下一个 10_000 行。
现在 rownum 列出现在结果中，我不想看到它。由于我对 SQL 不是很有经验，这是我的问题:

为什么(这是我在搜索 SO 之前的第一次尝试)Select * From Person Where rownum > 100 and rownum < 110;返回 0 行？

为什么没有简单的方法来做 Select ... FROM ... WHERE rownum BETWEEN lowerBound AND upperBound 之类的事情?

如何摆脱r结果值中的列？从那里 SQL exclude a column using SELECT * [except columnA] FROM tableA?我显然需要创建一个 View 或一个临时表，但是考虑我的查询还有另一种方法吗？

它是否确保正确的分页？我读了 this article “使用 ROWNUM 分页”部分说我应该按独特的方式对值进行排序以获得一致的分页(所以我想按 rownum 排序很好，如果你能确认的话)。这不是违背了使用 FIRST_ROWS(N) 的目的吗？ ?

我希望这不是太多，我可以分成单独的问题，但我认为将它们折叠起来是相关的，因为它们是密切相关的。

Answer 1

您有4个问题，都是围绕的用法和功能展开的。行号 .每个问题我都会一一解答。

Why (this was my first attempt until I search on SO) Select * From Person Where rownum > 100 and rownum < 110; returns 0 rows ?

Thomas Kyte 关于 ROWNUM 和分页的很好的解释 here .

A 行号 值通过 后分配给一行谓词阶段 查询但在查询进行任何排序或聚合之前。此外，ROWNUM 值仅在分配后才会递增，这就是以下查询永远不会返回行的原因:

select * 
  from t 
 where ROWNUM > 1;

因为第一行的 ROWNUM > 1 不成立，所以 ROWNUM 不会前进到 2。因此，ROWNUM 值永远不会大于 1。

Why there is no simple way to do something like Select ... FROM ... WHERE rownum BETWEEN lowerBound AND upperBound ?

就在这里。来自 甲骨文 12c 以后，您可以使用新的 Top-n 行限制 特征。 See my answer here .

例如，下面的查询将返回 之间的员工。第四高 直到 第七高薪按升序排列:

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>

How to get rid of the r column in the resulting values?

而不是 select * , 列出外部查询中所需的列名。对于经常使用查询的情况，创建 View 是一项简单的一次性事件。

或者，在 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>

Does it ensure correct pagination?

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

例如，

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 上使用新的行限制功能。

几个很好的例子 here .