oracle 从工资中选择rownum，其中rownum=3；
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3457370/
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
select rownum from salary where rownum=3;
提问by Jagan
How to retrieve third row from any table using "rownum" key word ( i am using oracle-10g)
如何使用“rownum”关键字从任何表中检索第三行（我使用的是 oracle-10g）
回答by Bob Jarvis - Reinstate Monica
Oracle assigns values to ROWNUM sequentially as rows are produced by the query - thus, the first row fetched gets ROWNUM=1, the second row fetched gets ROWNUM=2, the third row fetched gets ROWNUM=3, etc. Notice - for a row to be assigned ROWNUM=3 two preceding rows MUST be fetched. And this is why your query returns no rows. You're asking the database for the third row fetched - but rows 1 and 2 have never been fetched.
当查询生成行时，Oracle 按顺序为 ROWNUM 赋值 - 因此，提取的第一行获得 ROWNUM=1，提取的第二行获得 ROWNUM=2，提取的第三行获得 ROWNUM=3，等等。注意——对于一行要分配 ROWNUM=3 必须提取前两行。这就是您的查询不返回任何行的原因。您正在向数据库询问提取的第三行 - 但从未提取过第 1 行和第 2 行。
To demonstrate, try running the following queries:
为了演示，请尝试运行以下查询：
SELECT S.* FROM SALARY S; -- Should return all rows
SELECT ROWNUM, S.* FROM SALARY S; -- Should return all rows with ROWNUM prepended
SELECT ROWNUM, S.* FROM SALARY WHERE ROWNUM=3; -- Should return no rows
To work around your problem, try the following:
要解决您的问题，请尝试以下操作：
SELECT ROW_NUMBER FROM
(SELECT ROWNUM AS ROW_NUMBER, S.* FROM SALARY S)
WHERE ROW_NUMBER = 3;
Share and enjoy.
分享和享受。
回答by Martin Smith
You would need to dosomething like this
你需要做这样的事情
select rnum,sal
from
( select sal, rownum rnum
from salary
order by sal desc )
where rnum = 3;
rownumis not assigned until after the predicate phase so rownum = 3will always be false. Use a CTE or derived table then you can access the rownumfrom outside it.
rownum直到谓词阶段之后才分配，因此rownum = 3将始终为假。使用 CTE 或派生表，然后您可以rownum从它外部访问。
回答by Ashish Bakshi
SELECT ROW_NUMBER FROM (SELECT ROWNUM AS ROW_NUMBER, S.* FROM SALARY S) WHERE ROW_NUMBER = 3;
This is incorrect. This will always return '3', because you are selecting ROW_NUMBER.
这是不正确的。这将始终返回“3”，因为您选择的是 ROW_NUMBER。
It should instead be "select *", as mentioned below:
它应该是“select *”，如下所述：
select * from (select rownum as row_number, s.* from salary s) where row_number = 3;
回答by Tobiasopdenbrouw
Is rownum an actual column in your salary table? If not, depending on your DB type, rownum is likely not supported.
rownum 是工资表中的实际列吗？如果没有，根据您的数据库类型，可能不支持 rownum。
