oracle ROWNUM 如何在分页查询中工作？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30321483/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How ROWNUM works in pagination query?
提问by user2336315
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).
所以我想在 Oracle DB 中选择一系列行。我需要这样做，因为我在表中有数百万行，我想将结果分页给用户（如果您知道在客户端执行此操作的另一种方法，如果重要，我正在使用 JavaFX，但我不认为通过网络发送所有数据以在客户端对它们进行分页是个好主意）。
So after reading this post: SQL ROWNUM how to return rows between a specific range, I have the following query:
所以在阅读这篇文章后：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;
The 100and 110are 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.
该100和110只是例子。在应用程序中，我只要求下限并添加 10_000 的大小以获取下一个 10_000 行。
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:
现在 rownum 列出现在结果中，我不想看到它。由于我对 SQL 不是很有经验，所以我的问题是：
- 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
rcolumn 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 articlesection "Pagination with ROWNUM", that said I should order the values by something unique to get a consistent pagination (so I guess ordering by
rownumis fine, if you can confirm). Doesn't it defeat the purpose of usingFIRST_ROWS(N)?
- 为什么（这是我在搜索 SO 之前的第一次尝试）
Select * From Person Where rownum > 100 and rownum < 110;返回 0 行？ 为什么没有简单的方法来做这样的事情
Select ... FROM ... WHERE rownum BETWEEN lowerBound AND upperBound？如何摆脱
r结果值中的列？从那里SQL 使用 SELECT * [except columnA] FROM tableA 排除一列？我显然需要创建一个视图或临时表，但是考虑我的查询还有另一种方法吗？它是否确保正确的分页？我读了这篇文章“Pagination with ROWNUM”部分，它说我应该按独特的东西对值进行排序以获得一致的分页（所以我猜排序
rownum很好，如果你能确认的话）。它不会违背使用的目的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.
我希望这不是太多，我可以分成单独的问题，但我认为将它们折叠起来是相关的，因为它们是密切相关的。
Thanks :)
谢谢 ：）
回答by Lalit Kumar B
You have 4 questions, and all revolve around the usage and functionality of ROWNUM. I will answer each question one-by-one.
您有 4 个问题，都围绕ROWNUM的用法和功能展开。每个问题我都会一一解答。
Why (this was my first attempt until I search on SO) Select * From Person Where rownum > 100 and rownum < 110; returns 0 rows ?
为什么（这是我在搜索 SO 之前的第一次尝试） Select * From Person Where rownum > 100 and rownum < 110; 返回 0 行？
Nice explanation by Thomas Kyte regarding ROWNUM and pagination here.
Thomas Kyte 在此处对 ROWNUM 和分页进行了很好的解释。
A ROWNUMvalue is assigned to a row after it passes the predicate phaseof 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:
甲ROWNUM它通过后值被分配给行谓词相的查询，但查询之前是否有任何分选或聚集。此外，ROWNUM 值仅在分配后才会递增，这就是以下查询永远不会返回行的原因：
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.
因为第一行的 ROWNUM > 1 不成立，所以 ROWNUM 不会前进到 2。因此，ROWNUM 值永远不会大于 1。
Why there is no simple way to do something like Select ... FROM ... WHERE rownum BETWEEN lowerBound AND upperBound ?
为什么没有简单的方法来做像 Select ... FROM ... WHERE rownum BETWEEN lowerBound AND upperBound 之类的事情？
Yes, there is. From Oracle 12conwards, you could use the new Top-n Row limitingfeature. See my answer here.
就在这里。从Oracle 12c开始，您可以使用新的Top-n Row 限制功能。在这里看到我的答案。
For example, the below query would return the employees between 4th highesttill 7th highest salariesin ascending order:
例如，下面的查询将按升序返回第 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>
How to get rid of the r column in the resulting values?
如何摆脱结果值中的 r 列？
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.
代替select *，在外部查询中列出所需的列名。对于经常使用查询的情况，创建视图是一项简单的一次性活动。
Alternatively, in SQL*Plusyou could use the NOPRINTcommand. It will not display the column name you don't want to display. However, it would only work in SQL*Plus.
或者，SQL*Plus您可以使用NOPRINT命令。它不会显示您不想显示的列名。但是，它只能在 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.
或者，如上所示，在 12c 上使用新的行限制功能。
Few good examples here.
这里很少有好的例子。
回答by Rob van Laarhoven
Answer to question 2: In Oracle 12 you can use pagination
问题 2 的回答：在 Oracle 12 中，您可以使用分页
select owner, object_name, object_id
from t
order by owner, object_name
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;
回答by Pavel Zimogorov
I usually write a query like this:
我通常写这样的查询：
select *
from
(
select a.*, rownum as rn
from table_name a
where rownum <= 110
)
where rn > 100
