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

2021-12-01 00:00:00 sql oracle pagination rownum

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

所以在阅读这篇文章后:SQL ROWNUM 如何要返回特定范围之间的行，我有以下查询:

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

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

现在结果中出现 rownum 列，我不想看到它.由于我对 SQL 不是很熟悉，所以我的问题是:

为什么(这是我在搜索 SO 之前的第一次尝试)Select * From Person Where rownum >100 和 rownum <110;返回0行?
为什么没有像 Select ... FROM ... WHERE rownum BETWEENlowerBound AND upperBound 这样的简单方法?
如何去除结果值中的 r 列?从那里 SQL 排除列使用 SELECT * [除了 columnA] FROM tableA?我显然需要创建一个视图或一个临时表，但还有其他方法考虑我的查询吗?
它是否确保正确的分页?我阅读了这篇文章部分"使用 ROWNUM 进行分页"，这表示我应该通过一些独特的东西对值进行排序以获得一致的分页(所以我猜按 rownum 排序是好的，如果你能确认的话).这不是违背了使用 FIRST_ROWS(N) 的目的吗?

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

解决方案

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

<块引用>

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

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

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

选择 *从T其中 ROWNUM >1;

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

<块引用>

为什么没有简单的方法可以执行 Select ... FROM ... WHERE rownum BETWEENlowerBound AND upperBound 之类的操作?

是的，有.从 Oracle 12c 开始，您可以使用新的Top-n Row 限制功能.在此处查看我的答案.

例如，以下查询将按升序返回第 4 高到第 7 高工资之间的员工:

SQL>选择 empno, 萨尔2 发件人3 由萨尔订购4 偏移 4 行只取下 4 行；EMPNO SAL---------- ----------7654 12507934 13007844 15007499 1600SQL>

<块引用>

如何去掉结果值中的 r 列?

在外部查询中列出所需的列名，而不是 select *.对于经常使用查询的情况，创建视图是一项简单的一次性活动.

或者，在 SQL*Plus 中，您可以使用 NOPRINT 命令.它不会显示您不想显示的列名.但是，它只能在 SQL*Plus 中工作.

例如

COLUMN column_name NOPRINT

例如

SQL>描述部姓名为空?类型----------------------------------------- -------- ------------部门编号(2)DNAME VARCHAR2(14)LOC VARCHAR2(13)SQL>列名称 NOPRINTSQL>列位置 NOPRINTSQL>选择 * 从部门;德普诺----------10203040SQL>

<块引用>

它是否确保正确的分页?

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

例如

SELECT valFROM (SELECT val, rownum AS rnum从(选择值从T按 val 排序)WHERE rownum <= 8)其中 rnum > = 5;价值----------3344选择了 4 行.SQL>

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

几个很好的例子这里.

So I want to select a range of rows in an Oracle DB. I need to do this because I have millions of rows in the table and I want to paginate results to the user (if you know another way to do this on the client side, I'm using JavaFX if it matters but I don't think it's a good idea to send all the datas over the network to paginate them on the client side).

So after reading this post: SQL ROWNUM how to return rows between a specific range, I have the following query:

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

The 100 and 110 are just example. In the application, I just ask for the lower bound and add a size of 10_000 to fetch the next 10_000 rows.

Now the rownum column appears in the result and I don't want to see it. As I'm not very experienced with SQL, here's my questions:

Why (this was my first attempt until I search on SO) Select * From Person Where rownum > 100 and rownum < 110; returns 0 rows ?
Why there is no simple way to do something like Select ... FROM ... WHERE rownum BETWEEN lowerBound AND upperBound ?
How to get rid of the r column in the resulting values? From there SQL exclude a column using SELECT * [except columnA] FROM tableA? I need apparently to create a view or a temporary table, but is there another way considering my query?
Does it ensure correct pagination? I read this article section "Pagination with ROWNUM", that said I should order the values by something unique to get a consistent pagination (so I guess ordering by rownum is fine, if you can confirm). Doesn't it defeat the purpose of using FIRST_ROWS(N)?

I hope it's not too much, I could split into separate questions, but I think it's relevant to have them collapsed as they are closely related.

解决方案

You have 4 questions, and all revolve around the usage and functionality of ROWNUM. I will answer each question one-by-one.

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

Nice explanation by Thomas Kyte regarding ROWNUM and pagination here.

A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned, which is why the following query will never return a row:

select * 
  from t 
 where ROWNUM > 1;

Because ROWNUM > 1 is not true for the first row, ROWNUM does not advance to 2. Hence, no ROWNUM value ever gets to be greater than 1.

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

Yes, there is. From Oracle 12c onwards, you could use the new Top-n Row limiting feature. See my answer here.

For example, the below query would return the employees between 4th highest till 7th highest salaries in ascending order:

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?

Instead of select *, list the required column names in the outer query. For frequently using the query, creating a view is a simple one time activity.

Alternatively, in SQL*Plus you could use the NOPRINT command. It will not display the column name you don't want to display. However, it would only work in SQL*Plus.

For example,

COLUMN column_name NOPRINT

For example,

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?

Yes, if you write the pagination query correctly.

For example,

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>

Or, use the new row limiting feature on 12c as I have shown above.

Few good examples here.

相关文章