SQL 如何使用rownum
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15963129/
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
How to use rownum
提问by Dhruva
I have a employee table in oracle with name,salary and other details.
我在 oracle 中有一个员工表，其中包含姓名、薪水和其他详细信息。
I am trying to get the second highest salary but not able to fetch.
我试图获得第二高的薪水，但无法获得。
This one working fine
这个工作正常
with e_salary as (select distinct salary from employee)
select salary from e_salary
order by salary desc
And gives output:
并给出输出：
450000
61000
60000
50000
40000
30000
20000
6000
but when i am using the same query to fetch second highest row not getting any output
但是当我使用相同的查询来获取第二高的行时没有得到任何输出
select salary
from ( with e_salary as (select distinct salary from employee)
select salary from e_salary order by salary desc)
where rownum = 2
but as i replace the rownum=2with rownum<2it gives output of first two records. Please someone explain why rownum=2is not working
但是当我rownum=2用rownum<2它替换时，它给出了前两条记录的输出。请有人解释为什么rownum=2不起作用
回答by SenthilPrabhu
This will work:
这将起作用：
select salary from ( select salary , rownum as rn from (select salary from e_salary order by salary desc)) where rn = 2;
选择薪水从（选择薪水，rownum as rn from（从e_salary order bysalary desc中选择薪水））其中rn = 2;
Why it doesn't work:
为什么它不起作用：
When assigning ROWNUM to a row, Oracle starts at 1 and only 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.
给一行赋值ROWNUM时，oracle从1开始，只有在选中一行时才会增加值；也就是说，当 WHERE 子句中的所有条件都满足时。由于我们的条件要求 ROWNUM 大于 2，因此不会选择任何行并且 ROWNUM 的增量永远不会超过 1。
Hope u are clear right now.
希望你现在清楚。
回答by Kamal
Use of rownum is a tricky affair. Safest bet is to use it only when you want to limit the number of results to be shown. For example rownum<2 or rownum<=5.
使用 rownum 是一件棘手的事情。最安全的做法是仅在您想限制要显示的结果数量时才使用它。例如 rownum<2 或 rownum<=5。
Why rownum=2 will not work?
为什么 rownum=2 不起作用？
Read here - http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html
在这里阅读 - http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html
In summary, this is how oracle execute a query
总之，这就是oracle执行查询的方式
- The FROM/WHERE clause goes first.
- ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
- SELECT is applied.
- GROUP BY is applied.
- HAVING is applied.
- ORDER BY is applied.
- FROM/WHERE 子句排在第一位。
- ROWNUM 被分配并递增到来自 FROM/WHERE 子句的每个输出行。
- SELECT 已应用。
- 应用了 GROUP BY。
- 应用了 HAVING。
- 应用了 ORDER BY。
rownum<=2 clause will get converted to
rownum<=2 子句将被转换为
ROWNUM = 1
for x in
( select * from emp )
loop
exit when NOT(ROWNUM <= 2)
OUTPUT record to temp
ROWNUM = ROWNUM+1
end loop
SORT TEMP
if you change exit when NOT(ROWNUM <= 2) with rownnum=2, you can see it will fail in the first run itself
如果您在 NOT(ROWNUM <= 2) 和 rownnum=2 时更改退出，您可以看到它在第一次运行时会失败
So if I cannot use rownum, what can I use. Try using row_number() http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm
所以如果我不能使用rownum，我可以使用什么。尝试使用 row_number() http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm
It works something like
它的工作原理类似于
SELECT last_name FROM
(SELECT last_name, ROW_NUMBER() OVER (ORDER BY last_name) R FROM employees)
WHERE R BETWEEN 51 and 100;
回答by Freelancer
select ename ,sal ,rank() over (order by sal desc) ranking from emp;
Try this one.
试试这个。
Follow this link, all the things regarding nth highest row is given over here in oracle:
按照此链接，在 oracle 中提供了有关第 n 个最高行的所有内容：
回答by Ed Gibbs
rownumin a condition stops evaluating the first time it fails. On the first row returned, rownumis 1, therefore it fails the rownum = 2test and stops trying. There's an excellent post about it here.
rownum条件在第一次失败时停止评估。在返回的第一行上，rownum是 1，因此它未通过rownum = 2测试并停止尝试。有一个关于它的优秀岗位在这里。
To get the second-highest salary, use the Oracle analytical DENSE_RANKfunction:
要获得第二高的薪水，请使用 Oracle 分析DENSE_RANK函数：
SELECT DISTINCT Salary FROM (
SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM e_salary)
WHERE SalaryRank = 2
Note that if there's a tie for second, the query could return more than one value. That's why the outer SELECTis a SELECT DISTINCT.
请注意，如果第二次出现并列，则查询可能会返回多个值。这就是为什么外部SELECT是一个SELECT DISTINCT.
回答by OQJF
First you should understand what the rownumis. Let me give you an example,
首先你应该明白是什么rownum。让我给你举个例子，
you want to get data with a filter and rownum=2,
first Oracle executes the sql with filter and get the first record,
give it the rownum 1,
and then compare it the rownum filter rownum=2, which doesn't match, so discard record,
then get second record, give it rownum=1(if the first record is matched then the rownum will be 2) too, then do the compare............
So you can find the reason.
这样你就可以找到原因了。
回答by liza
Without using rownumcommand you can get the second highest salary by using this query:
在不使用rownum命令的情况下，您可以使用以下查询获得第二高的薪水：
select MAX(Salary) from Employee
WHERE Salary NOT IN
(select MAX(Salary) from Employee )
or,
或者，
select MAX(Salary) from Employee
WHERE Salary <>
(select MAX(Salary) from Employee )
query for nth highest:
查询第 n 个最高值：
SELECT * FROM Employee Emp1
WHERE (N-1) =
(SELECT COUNT(DISTINCT(Emp2.Salary))FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)
回答by Shahnwaz Alam
From what I understand, rownum numbers the rows in a result set.
据我了解，rownum 对结果集中的行进行编号。
So, in your example:
所以，在你的例子中：
select * from table1 where rownum=2
select * from table1 where rownum=2
How many rows are there going to be in the result set? Therefore, what rownum would be assigned to such a row? Can you see now why no result is actually returned?
结果集中会有多少行？因此，什么 rownum 将分配给这样的一行？你现在能明白为什么实际上没有返回结果吗？
In general, you should avoid relying on rownum, or any features that imply an order to results. Try to think about working with the entire set of results.
通常，您应该避免依赖 rownum 或任何暗示结果顺序的功能。尝试考虑使用整个结果集。
With that being said, I believe the following would work:
话虽如此，我相信以下方法可行：
select * from (select rownum as rn,table1.* from table1) as t where t.rn = 2
select * from (select rownum as rn,table1.* from table1) as t where t.rn = 2
Because in that case, you're numbering the rows within the subquery.
因为在这种情况下，您要对子查询中的行进行编号。
