sql - 使用 rownum 选择表格的第二行

Question

我尝试了以下查询:

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 过滤器时不返回任何记录？

Answer 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 <= 10;

While queries with these conditions will always return zero rows.

.. WHERE rownum = 2;

.. WHERE rownum > 10;

引自 Understanding Oracle rownum

您应该以这种方式修改您的查询以便工作:

select empno
from
    (
    select empno, rownum as rn 
    from (
          select empno
          from emp
          order by sal desc
          )
    )
where rn=2;

编辑 :我已经更正了查询以获得 rownum 后 Sal desc 的订单