SQL 在oracle中使用rownum获取数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7753936/
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
fetching data using rownum in oracle
提问by Yagnesh Agola
I have a query in oracle to fetch data from table using rownumbut i didn't get any data.
My query is like this :
我在 oracle 中有一个查询来使用从表中获取数据，rownum但我没有得到任何数据。
我的查询是这样的：
select * from table-name where rownum<5
select * from table-name where rownum<5
is this is a wrong query to fetch data whose row number is less than 5.
when i used query like :select * from table-name where rownum<=4
than it will gives a result record.
这是获取行号小于 5 的数据的错误查询吗。
当我使用这样的查询时：select * from table-name where rownum<=4
比它会给出结果记录。
My question is what is wrong here with ?
Is this is syntax error or anything else??..
我的问题是这里有什么问题？
这是语法错误还是其他什么？？..
回答by rahularyansharma
rownum is a pseudo column that counts rows in the result set after the where clause has been applied.
rownum 是一个伪列，用于在应用 where 子句后对结果集中的行进行计数。
SELECT table_name
FROM user_tables
WHERE rownum > 2;
TABLE_NAME
------------------------------
0 rows selected
However, this query will always return zero rows, regardless of the number of rows in the table.
但是，无论表中有多少行，此查询将始终返回零行。
To explain this behaviour, we need to understand how Oracle processes ROWNUM. When assigning ROWNUM to a row, Oracle starts at 1 and only increments the value when a row is selected; that is, when all conditions in the WHERE clause are met. Since our condition requires that ROWNUM is greater than 2, no rows are selected and ROWNUM is never incremented beyond 1.
为了解释这种行为，我们需要了解 Oracle 如何处理 ROWNUM。给一行赋值ROWNUM时，Oracle从1开始，只有在选中一行时才递增该值；也就是说，当 WHERE 子句中的所有条件都满足时。由于我们的条件要求 ROWNUM 大于 2，因此不会选择任何行并且 ROWNUM 的增量永远不会超过 1。
http://blog.lishman.com/2008/03/rownum.html
http://blog.lishman.com/2008/03/rownum.html
Edited
已编辑
this paragraph i find on oraclewebsite which is much better
我在oracle网站上找到的这一段要好得多
Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:
测试大于正整数的 ROWNUM 值的条件始终为假。例如，此查询不返回任何行：
SELECT * FROM employees
WHERE ROWNUM > 1;
The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.
获取的第一行被分配为 1 的 ROWNUM，并使条件为假。要提取的第二行现在是第一行，并且还分配了一个 ROWNUM 为 1，并使条件为假。随后所有行都无法满足条件，因此不会返回任何行。
You can also use ROWNUM to assign unique values to each row of a table, as in this example:
您还可以使用 ROWNUM 为表的每一行分配唯一值，如下例所示：
回答by BigMike
Syntax seems correct to me.
语法对我来说似乎是正确的。
However ROWNUM is calculated on result rows for example:
但是 ROWNUM 是在结果行上计算的，例如：
SELECT * FROM TABLE_NAME WHERE ROWNUM < 10 ORDER BY TABLE_FIELD ASC;
and
和
SELECT * FROM TABLE_NAME WHERE ROWNUM < 10 ORDER BY TABLE_FIELD DESC;
Will give you different results.
会给你不一样的结果。
Each time a query is executed, for each tuple, Oracle assigns a ROWNUM which is scopet to that only query.
每次执行查询时，对于每个元组，Oracle 都会分配一个 ROWNUM，该 ROWNUM 仅适用于该查询。
What are you trying to accomplish ?
你想达到什么目的 ？
回答by ivanatpr
Since for the reasons rahularyansharma mentions, rownum based queries won't always function the way you might expect, a way around this is to do something like
由于 rahularyansharma 提到的原因，基于 rownum 的查询并不总是像您期望的那样运行，解决这个问题的方法是做类似的事情
SELECT * from (SELECT rownum AS rn, TABLE_NAME.* FROM TABLE_NAME)
where rn > 5;
However, be aware that this will be a fairly inefficient operation when operating over large datasets.
但是，请注意，在对大型数据集进行操作时，这将是一个相当低效的操作。
