oracle 如果测试 rownum 的值大于 100，则表 rownum 返回 0 条记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13351318/
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
Table rownum returns 0 records if rownum is tested for value greater than 100
提问by ziggy
Possible Duplicate:
Rownum not working with query
可能的重复：
Rownum 不适用于查询
I am running some tests for how rownumworks. Please could someone explain the output of the following queries - Specifically why the third query is returning 0 rows.
我正在运行一些测试如何rownum工作。请有人解释以下查询的输出 - 特别是为什么第三个查询返回 0 行。
Query 1 - Returns 2456 rows
查询 1 - 返回 2456 行
Select rownum, s.id, s.title, sv.version_id
from software s JOIN software_version sv on (s.id = sv.id);
Query 2 - Returns 100 rows
查询 2 - 返回 100 行
Select rownum, s.id, s.title, sv.version_id
from software s JOIN software_version sv on (s.id = sv.id)
where rownum between 1 and 100;
Query 3 - Returns 0 rows
查询 3 - 返回 0 行
Select rownum, s.id, s.title, sv.version_id
from software s JOIN software_version sv on (s.id = sv.id)
where rownum between 101 and 400;
Thanks
谢谢
回答by DazzaL
rownum is only evaluated AFTER the row is fetched and other predicates are evaluated. that is the key. so if you say select * from table where rownum >= 2;
rownum 仅在获取行并评估其他谓词后评估。这是关键。所以如果你说 select * from table where rownum >= 2;
it can never work, as it works like
它永远无法工作，因为它像
open cursor loop
fetch row (rownum is evaluated as the last thing here)
if fetched then rownum = rownum + 1
end cursor
if you request rownum starting from > 1 then its never true. to do pagination if you need to you have to code like
如果您请求从 > 1 开始的 rownum，那么它永远不会为真。如果需要的话，要进行分页，你必须像
select *
from (select rownum r, t.*
from your query tables
order by ..)
where r>=101
and rownum <= 400
also read more here at ask tom
也可以在询问汤姆这里阅读更多信息
