SQL Oracle从表中选择第N行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4509167/
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 Nth Row From A Table In Oracle
提问by Niraj Choubey
How can I select the Nth row from a table in Oracle?
如何从 Oracle 中的表中选择第 N 行？
I tried
我试过
SELECT PRICE FROM AAA_PRICING WHERE ROWNUM = 2
but that didn't work. Please help!
但这没有用。请帮忙！
回答by davek
Based on the classic answer:
基于经典答案：
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:127412348064
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:127412348064
select *
from ( select a.*, rownum rnum
from ( YOUR_QUERY_GOES_HERE -- including the order by ) a
where rownum <= N_ROWS )
where rnum >= N_ROWS
/
回答by Michael Pakhantsov
Will not works with '=' (will works <2 or >2, but not equal)
不适用于“=”（适用于 <2 或 >2，但不相等）
so you can
这样你就可以
SELECT Price from (SELECT PRICE, ROWNUM AS RN FROM AAA_PRICING) WHERE RN = 2
回答by sjngm
To address the reason for this:
要解决此问题的原因：
The RowNumis a pseudo-column supplied by Oracle. It is generated while the SELECT-clause is being processed. Since the WHERE-clause is handled beforethe SELECT-clause, the RowNumdoes not have a proper value yet.
的RowNum是由Oracle提供的伪柱。它是在处理 SELECT 子句时生成的。由于 WHERE 子句在 SELECT 子句之前处理，因此RowNum还没有正确的值。
One can argue whether or not it makes sense to have Oracle throw an exception in situation, but because RowNumstill is a pseudo-column it's still valid to have it there.
人们可能会争论在这种情况下让 Oracle 抛出异常是否有意义，但因为它RowNum仍然是一个伪列，所以它仍然有效。
Note: Don't confuse this with RowId, which is an entire different story!
注意：不要将此与 混淆RowId，这是完全不同的故事！
IMPORTANT EDIT:
重要编辑：
Note that what I wrote about RowNumis only true for =, >, >=, IN ()and maybe others. If you check for, e.g. RowNum < 10, you only get nine records!? I don't know why that is the case!
请注意，我写了一篇关于RowNum只有真正的=，>，>=，IN ()或者其他人。如果你检查，例如RowNum < 10，你只会得到九个记录！？我不知道为什么会这样！
回答by Art
Select * From
(
Select Row_Number() OVER (Order by empno) rno, e.*
From scott.emp e
)
Where rno in (1, 3, 11)
回答by stjohnroe
SELECT PRICE
FROM (
SELECT PRICE,
ROWNUM rnum
FROM AAA_PRICING
ORDER BY PRICE ASC
)
WHERE rnum = 2
回答by Rakesh Anand
SELECT * FROM
(SELECT PRICE, ROWNUM AS RN FROM AAA_PRICING )
WHERE RN = 2;
回答by sunny
select * from (Select Price, rownum as rn from(Select * from AAA_PRICING a order by a.Price))
where rn=2;
It will give you 2nd lowest price from the Price column. If you want simply 2nd row remove Order By condition.
它将为您提供价格列中第二低的价格。如果您只想删除第 2 行的 Order By 条件。
回答by Sudeep Mohan Nayak
ROWNUMis a pseudo column which generates unique pseudo values (equals to the number of records present in the SELECTstatement o/p) during the execution of SELECTclause. When this pseudo column is specified with the WHEREclause it's value becomes 1by default. So it behaves according to the comparison operator specified with it.
ROWNUM是一个伪列，它SELECT在执行SELECT子句期间生成唯一的伪值（等于语句 o/p 中存在的记录数）。当这个伪列用WHERE子句指定时，它的值变为1默认值。因此，它的行为取决于用它指定的比较运算符。
SELECT * FROM (
SELECT ROWNUM RN, E.*
FROM Emp E
)
WHERE RN = 10;
回答by Singh Kailash
select *
From (select PRICE, DENSE_RANK() over(ORDER BY PRICE desc) as RNO
From AAA_PRICING
) t where RNO=2;
回答by sideyn
If you are on Oracle 12 or above, You can use the result offset and fetch clauses:
如果您使用的是 Oracle 12 或更高版本，则可以使用 result offset 和 fetch 子句：
SELECT PRICE FROM AAA_PRICING
offset 1 rows fetch next 1 rows only
