Oracle中rownum与order by的执行顺序

执行顺序

Oracle会先设置行的rownum，再进行order by
执行顺序：rownum > order by

举例

有以下数据，现在要倒序后取前三条记录。

SQL> select sysdate + level level_ from dual connect by level <= 10;

LEVEL_
-----------
2017/8/5 19
2017/8/6 19
2017/8/7 19
2017/8/8 19
2017/8/9 19
2017/8/10 1
2017/8/11 1
2017/8/12 1
2017/8/13 1
2017/8/14 1

错误的写法

select * from (select rownum rno, t.* from (select sysdate + level level_ from dual connect by level <= 10) t order by level_ desc) t1 where t1.rno <= 3;

如上所说，因为rownum比order by优先级高，Oracle会先执行进行rownum赋行值，如下

select rownum rno, t.* from (select sysdate + level level_ from dual connect by level <= 10) t;

结果：
       RNO LEVEL_
---------- -----------
         1 2017/8/5 19
         2 2017/8/6 19
         3 2017/8/7 19
         4 2017/8/8 19
         5 2017/8/9 19
         6 2017/8/10 1
         7 2017/8/11 1
         8 2017/8/12 1
         9 2017/8/13 1
        10 2017/8/14 1

再进行order by排序，如下：

RNO LEVEL_
---------- -----------
        10 2017/8/14 1
         9 2017/8/13 1
         8 2017/8/12 1
         7 2017/8/11 1
         6 2017/8/10 1
         5 2017/8/9 19
         4 2017/8/8 19
         3 2017/8/7 19
         2 2017/8/6 19
         1 2017/8/5 19

再取前三条记录，最终结果如下

RNO LEVEL_
---------- -----------
         3 2017/8/7 19
         2 2017/8/6 19
         1 2017/8/5 19

正确的写法

正确的写法应该是先order by，先包一层查询，再赋rownum值

select * from (select rownum rno, t1.* from (select * from (select sysdate + level level_ from dual connect by level <= 10) t order by level_ desc) t1) t2 where t2.rno <= 3 结果： RNO LEVEL_ ---------- ----------- 1 2017/8/14 1 2 2017/8/13 1 3 2017/8/12 1