使用 rownum 选择表格的第二行

2021-12-06 00:00:00 sql oracle oracle10g rownum

我尝试了以下查询:

select empno from (
                   select empno 
                     from emp
                    order by sal desc
                  )
where rownum = 2

这不会返回任何记录.

当我尝试这个查询时

 select rownum,empno from (
                        select empno from emp order by sal desc)

它给了我这个输出:

ROWNUM  EMPNO      
1       7802        
2       7809    
3       7813    
4       7823

谁能告诉我我的第一个查询有什么问题?为什么添加ROWNUM过滤器时不返回任何记录?

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?

推荐答案

为了解释这种行为，我们需要了解 Oracle 如何处理行号.给一行赋值 ROWNUM 时，Oracle 从 1 开始，仅在选择一行时增加值；也就是说，当所有WHERE 子句中的条件得到满足.由于我们的条件需要ROWNUM 大于 2，未选择任何行且 ROWNUM 为永远不会超过 1.

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 = 1;

... WHERE rownum <= 10;

.. WHERE rownum <= 10;

虽然具有这些条件的查询将始终返回零行.

While queries with these conditions will always return zero rows.

...WHERE rownum = 2;

.. WHERE rownum = 2;

... WHERE rownum > 10;

.. WHERE rownum > 10;

引自了解 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:我已经更正了查询以获取 rownum after 由 sal desc 排序

EDIT: I've corrected the query to get the rownum after the order by sal desc

相关文章