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 192017/8/6 192017/8/7 192017/8/8 192017/8/9 192017/8/10 12017/8/11 12017/8/12 12017/8/13 12017/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