Oracle/SQL：为什么查询“SELECT * FROM records WHERE rownum >= 5 AND rownum <= 10” - 返回零行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/855422/
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
Oracle/SQL: Why does query "SELECT * FROM records WHERE rownum >= 5 AND rownum <= 10" - return zero rows
提问by Vini
Why does the following query return 'zero' records:
为什么以下查询返回“零”记录：
SELECT * FROM records WHERE rownum >= 5 AND rownum <= 10
OR
SELECT * FROM records WHERE rownum >= 5
Whereas the following query return proper records:
而以下查询返回正确的记录：
SELECT * FROM records WHERE rownum <= 15
Regards,
- Ashish
问候，
- Ashish
回答by codeulike
In Oracle, Rownum values are assigned after the filtering stage of the query - they are not rows of the table, they are rows of the query result set.
在 Oracle 中，Rownum 值是在查询的过滤阶段之后分配的——它们不是表的行，而是查询结果集的行。
So the first row that comes back will always be given rownum 1, the second row that comes back rownum 2, etc.
因此，返回的第一行将始终为 rownum 1，返回的第二行为 rownum 2，依此类推。
The rownum value is only incremented after it is assigned, so any query like
rownum 值仅在分配后才递增，因此任何查询如
select * from t where ROWNUM > 1
will neverreturn any results. This query says 'I dont want to see the first row that gets returned to me, only the ones after that' which is sortof a paradox so nothing gets returned.
将永远不会返回任何结果。这个查询说'我不想看到返回给我的第一行，只有之后的那些'这有点悖论所以没有返回。
See Ask Tom:On ROWNUM and Limiting Resultsfor more details.
有关更多详细信息，请参阅Ask Tom:On ROWNUM 和 Limiting Results。
回答by Shivraaz
ROWNUM is a pseudocolumn and it's value will not be available at the time of query execution. So you can't filter your results based on this column. With that said, there are work arounds to mitigate it. Please see below (it is not recommended to use if your result is very huge).
ROWNUM 是一个伪列，它的值在查询执行时不可用。因此，您无法根据此列过滤结果。话虽如此，有一些解决方法可以缓解它。请看下面（如果你的结果非常大，不建议使用）。
Select a.* From
(
Select COL1, COL2, ROWNUM RowNumber From MyTable
) a
Where
RowNumber = 5;
回答by Matas Vaitkevicius
Alternative is to use MINUS
替代方法是使用 MINUS
SELECT * FROM records
WHERE ROWNUM <= 10
minus SELECT * FROM records
WHERE ROWNUM <= 5
this will filter out non-unique values so you better be selecting id.
这将过滤掉非唯一值，因此您最好选择 id。
Hope this saves you some time.
希望这可以为您节省一些时间。
