SQL 使用 rownum 选择表格的第二行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9240192/
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
Selecting the second row of a table using rownum
提问by Gaurav Soni
I have tried the below query:
我尝试了以下查询：
select empno from (
select empno
from emp
order by sal desc
)
where rownum = 2
This is not returning any records.
这不会返回任何记录。
When I tried this query
当我尝试这个查询时
select rownum,empno from (
select empno from emp order by sal desc)
It gives me this output:
它给了我这个输出：
ROWNUM EMPNO
1 7802
2 7809
3 7813
4 7823
Can anyone tell me what's the problem with my first query? Why is it not returning any records when I add the ROWNUM filter?
谁能告诉我我的第一个查询有什么问题？为什么添加 ROWNUM 过滤器时不返回任何记录？
回答by Florin Ghita
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.
The bottom line is that conditions such as the following will work as expected.
.. WHERE rownum = 1;
.. WHERE rownum <= 10;
While queries with these conditions will always return zero rows.
.. WHERE rownum = 2;
.. WHERE rownum > 10;
为了解释这种行为，我们需要了解 Oracle 如何处理 ROWNUM。给一行赋值ROWNUM时，Oracle从1开始，只有在选中一行时才递增该值；也就是说，当 WHERE 子句中的所有条件都满足时。由于我们的条件要求 ROWNUM 大于 2，因此不会选择任何行并且 ROWNUM 的增量永远不会超过 1。
最重要的是，以下条件将按预期工作。
.. WHERE rownum = 1;
.. WHERE rownum <= 10;
虽然具有这些条件的查询将始终返回零行。
.. WHERE rownum = 2;
.. 哪里 rownum > 10;
Quoted from Understanding Oracle rownum
You should modify you query in this way in order to work:
您应该以这种方式修改您的查询以便工作：
select empno
from
(
select empno, rownum as rn
from (
select empno
from emp
order by sal desc
)
)
where rn=2;
EDIT: I've corrected the query to get the rownum afterthe order by sal desc
编辑：我已经更正了查询以在按 Sal desc 的订单后获取 rownum
回答by Branko Dimitrijevic
In the first query, the first row will have ROWNUM = 1 so will be rejected. The second row will also have ROWNUM = 1 (because the row before was rejected) and also be rejected, the third row will also have ROWNUM = 1 (because all rows before it were rejected) and also be rejected etc... The net result is that all rows are rejected.
在第一个查询中，第一行的 ROWNUM = 1，因此将被拒绝。第二行也将有 ROWNUM = 1（因为前一行被拒绝）并且也被拒绝，第三行也将有 ROWNUM = 1（因为它之前的所有行都被拒绝）并且也被拒绝等......净结果是所有行都被拒绝。
The second queryshould not return the result you got. It should correctly assign ROWNUM afterORDER BY.
在第二个查询不应该返回你得到的结果。它应该在ORDER BY之后正确分配 ROWNUM 。
As a consequence of all this, you need to use not 2 but 3 levels of subqueries, like this:
因此，您需要使用的不是 2 个而是 3 个级别的子查询，如下所示：
SELECT EMPNO, SAL FROM ( -- Make sure row is not rejected before next ROWNUM can be assigned.
SELECT EMPNO, SAL, ROWNUM R FROM ( -- Make sure ROWNUM is assigned after ORDER BY.
SELECT EMPNO, SAL
FROM EMP
ORDER BY SAL DESC
)
)
WHERE R = 2
The result:
结果：
EMPNO SAL
---------------------- ----------------------
3 7813
回答by John Woo
回答by Md. Kamruzzaman
For nth row using rownum in oracle:
对于在 oracle 中使用 rownum 的第 n 行：
select * from TEST WHERE ROWNUM<=n
MINUS
select * from TEST WHERE ROWNUM<=(n-1);
Example for second row :
第二行示例：
select * from TEST WHERE ROWNUM<=2
MINUS
select * from TEST WHERE ROWNUM<=1;
回答by Nasir Uddin
Select Second Row From a Table in Oracle
Oracle从表中选择第二行
SELECT *
FROM (SELECT * FROM emp ORDER BY rownum DESC)
WHERE rownum=1
回答by Deepak R
select empno from(
select empno,rownum as rum
from emp,
order by sal desc
)
where rum=2;
select empno from(
select empno,rownum as rum
from emp,
order by sal desc
)
where rum=2;
回答by Sachin Patil
Select * From (SELECT *,
ROW_NUMBER() OVER(ORDER BY column_name DESC) AS mRow
FROM table_name
WHERE condition) as TT
Where TT.mRow=2;
回答by ash
You can use RANKor DENSE_RANKto achieve what you are trying to achieve here.
您可以使用RANK或DENSE_RANK来实现您在此处尝试实现的目标。
