order by与rownum

--如果的记录无序
SQL> select * from t_mid;
 
                                      A
---------------------------------------
                                     10
                                      9
                                     11
                                      8
                                      2
                                     27
 
6 rows selected

---以降序对记录集进行排序
SQL> select a,rownum from (select a from t_mid order by a desc);
 
                                      A     ROWNUM
--------------------------------------- ----------
                                     27          1
                                     11          2
                                     10          3
                                      9          4
                                      8          5
                                      2          6
 
6 rows selected

---获取上述降序记录集的排名第2和2名的记录
SQL> select a from (select a,rownum as rn from (select a from t_mid order by a desc)) where rn between 2 and 3;
 
                                      A
---------------------------------------
                                     11
                                     10
 
 

--注意：order by 基于列排序时，不会对rownum列进行排序，rownum的结果仍是插入记录为顺序进行编号，而非根据order by产生rownum的值
SQL> select a,rownum as rn from t_mid order by a desc;
 
                                      A         RN
--------------------------------------- ----------
                                     27          6
                                     11          3
                                     10          1
                                      9          2
                                      8          4
                                      2          5
 
6 rows selected

小结：1，先对原表的记录集进行order by 排序产生一个临时结果集
   
      2,基于上述的临时结果集产生一个添加rownum的列的临时结果集
     
      3,基于上述的rownum的临时结果集 获取指定排名的结果
     
     

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-761447/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-761447/