如何使用rownum [英] How to use rownum
问题描述
我在oracle中有一个employee表,其中包含姓名,工资和其他详细信息.
I have a employee table in oracle with name,salary and other details.
我正试图拿到第二高的薪水,但无法拿到.
I am trying to get the second highest salary but not able to fetch.
这个很好用
with e_salary as (select distinct salary from employee)
select salary from e_salary
order by salary desc
并给出输出:
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
但是当我用rownum<2
替换rownum=2
时,它给出了前两个记录的输出.请有人解释为什么rownum=2
无法正常工作
but as i replace the rownum=2
with rownum<2
it gives output of first two records. Please someone explain why rownum=2
is not working
推荐答案
这将起作用:
从(选择薪水中选择薪水,从(选择薪水中选择rownum作为rn 从e_salary顺序按薪水desc)),其中rn = 2;
select salary from ( select salary , rownum as rn from (select salary from e_salary order by salary desc)) where rn = 2;
为什么不起作用:
将ROWNUM分配给行时,Oracle从1开始,并且仅在选择行时才递增该值;也就是说,当WHERE子句中的所有条件都满足时.由于我们的条件要求ROWNUM大于2,所以不会选择任何行,并且ROWNUM永远不会增加到1以上.
希望你现在很清楚.
这篇关于如何使用rownum的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!