ORACLE 中ROWNUM（偽列）用法解析（轉載）

以下摘自《解析oracle的rownum》作者：chen_liang

對於rownum來說它是oracle系統順序分配為從查詢返回的行的編號，返回的第一行分配的是1，第二行是2，依此類推，這個偽欄位可以用於限制查詢返回的總行數，且rownum不能以任何表的名稱作為字首。

(1) rownum 對於等於某值的查詢條件
如果希望找到學生表中第一條學生的資訊，可以使用rownum=1作為條件。但是想找到學生表中第二條學生的資訊，使用rownum=2結果查不到資料。因為rownum都是從1開始，但是1以上的自然數在rownum做等於判斷是時認為都是false條件，所以無法查到rownum = n（n>1的自然數）。
SQL> select rownum,id,name from student where rownum=1;（可以用在限制返回記錄條數的地方，保證不出錯，如：隱式遊標）
SQL> select rownum,id,name from student where rownum =2;
    ROWNUM ID     NAME
---------- ------ ---------------------------------------------------

（2）rownum對於大於某值的查詢條件
   如果想找到從第二行記錄以後的記錄，當使用rownum>2是查不出記錄的，原因是由於rownum是一個總是從1開始的偽列，Oracle 認為rownum> n(n>1的自然數)這種條件依舊不成立，所以查不到記錄。

查詢到第二行以後的記錄可使用以下的子查詢方法來解決。注意子查詢中的rownum必須要有別名，否則還是不會查出記錄來，這是因為rownum不是某個表的列，如果不起別名的話，無法知道rownum是子查詢的列還是主查詢的列。
SQL>select * from(select rownum no ,id,name from student) where no>2;
        NO ID     NAME
---------- ------ ---------------------------------------------------
         3 200003 李三
         4 200004 趙四

（3）rownum對於小於某值的查詢條件
rownum對於rownum1的自然數）的條件認為是成立的，所以可以找到記錄。
SQL> select rownum,id,name from student where rownum <3;
    ROWNUM ID     NAME
---------- ------ ---------------------------------------------------
        1 200001 張一
        2 200002 王二

查詢rownum在某區間的資料，必須使用子查詢。例如要查詢rownum在第二行到第三行之間的資料，包括第二行和第三行資料，那麼我們只能寫以下語句，先讓它返回小於等於三的記錄行，然後在主查詢中判斷新的rownum的別名列大於等於二的記錄行。但是這樣的操作會在大資料集中影響速度。
SQL> select * from (select rownum no,id,name from student where rownum<=3 ) where no >=2;
        NO ID     NAME
---------- ------ ---------------------------------------------------
         2 200002 王二
         3 200003 李三

（4）rownum和排序  
Oracle中的rownum的是在取資料的時候產生的序號，所以想對指定排序的資料去指定的rowmun行資料就必須注意了。
SQL> select rownum ,id,name from student order by name;
    ROWNUM ID     NAME
---------- ------ ---------------------------------------------------
         3 200003 李三
         2 200002 王二
         1 200001 張一
         4 200004 趙四
可以看出，rownum並不是按照name列來生成的序號。系統是按照記錄插入時的順序給記錄排的號，rowid也是順序分配的。為了解決這個問題，必須使用子查詢；
SQL> select rownum ,id,name from (select * from student order by name);
    ROWNUM ID     NAME
---------- ------ ---------------------------------------------------
         1 200003 李三
         2 200002 王二
         3 200001 張一
         4 200004 趙四
這樣就成了按name排序，並且用rownum標出正確序號（有小到大）
筆者在工作中有一上百萬條記錄的表，在jsp頁面中需對該表進行分頁顯示，便考慮用rownum來作，下面是具體方法(每頁顯示20條)：
“select * from tabname where rownum<20 order by name" 但卻發現oracle卻不能按自己的意願來執行，而是先隨便取20條記錄，然後再order by，後經諮詢oracle,說rownum確實就這樣，想用的話，只能用子查詢來實現先排序，後rownum，方法如下：
"select * from (select * from tabname order by name) where rownum<20",但這樣一來，效率會低很多。
後經筆者試驗，只需在order by 的欄位上加主鍵或索引即可讓oracle先按該欄位排序，然後再rownum；方法不變：    “select * from tabname where rownum<20 order by name"

取得某列中第N大的行

select column_name from
(select table_name.*,dense_rank() over (order by column desc) rank from table_name)
where rank = &N；
　假如要返回前5條記錄：

　　select * from tablename where rownum<6;(或是rownum <= 5 或是rownum != 6)
假如要返回第5-9條記錄：

select * from tablename
where …
and rownum<10
minus
select * from tablename
where …
and rownum<5
order by name
選出結果後用name排序顯示結果。(先選再排序)

注意：只能用以上符號(

select * from tablename where rownum != 10;返回的是前９條記錄。
不能用：>,>=,=,Between...and。由於rownum是一個總是從1開始的偽列，Oracle 認為這種條件不成立。

另外，這個方法更快：

select * from (
select rownum r,a from yourtable
where rownum <= 20
order by name )
where r > 10
這樣取出第11-20條記錄!(先選再排序再選)

要先排序再選則須用select巢狀：內層排序外層選。
rownum是隨著結果集生成的，一旦生成，就不會變化了；同時,生成的結果是依次遞加的，沒有1就永遠不會有2!
rownum 是在查詢集合產生的過程中產生的偽列，並且如果where條件中存在 rownum 條件的話，則:

1： 假如判定條件是常量，則：
只能 rownum = 1, <= 大於1 的自然數， = 大於1 的數是沒有結果的；大於一個數也是沒有結果的
即 當出現一個 rownum 不滿足條件的時候則 查詢結束 this is stop key（一個不滿足，系統將該記錄過濾掉，則下一條記錄的rownum還是這個，所以後面的就不再有滿足記錄，this is stop key）；

2： 假如判定值不是常量，則：

若條件是 = var , 則只有當 var 為1 的時候才滿足條件，這個時候不存在 stop key ,必須進行full scan ,對每個滿足其他where條件的資料進行判定，選出一行後才能去選rownum=2的行……

 

以下摘自《中國IT實驗室》

1.在ORACLE中實現SELECT TOP N

   由於ORACLE不支援SELECT TOP語句，所以在ORACLE中經常是用ORDER BY跟ROWNUM的組合來實現SELECT TOP N的查詢。

簡單地說，實現方法如下所示：

SELECT　列名１．．．列名ｎ　FROM

    (SELECT　列名１．．．列名ｎ　FROM 表名 ORDER BY 列名１．．．列名ｎ)

   WHERE ROWNUM <= N（抽出記錄數）

ORDER BY ROWNUM ASC

   下面舉個例子簡單說明一下。

顧客表customer(id,name)有如下資料：

ID NAME

   01 first

   02 Second

   03 third

   04 forth

   05 fifth

   06 sixth

   07 seventh

   08 eighth

   09 ninth

   10 tenth

   11 last

   則按NAME的字母順抽出前三個顧客的SQL語句如下所示：

SELECT * FROM

    (SELECT * FROM CUSTOMER ORDER BY NAME)

   WHERE ROWNUM <= 3

   ORDER BY ROWNUM ASC

   輸出結果為：

ID NAME

   08 eighth

   05 fifth

   01 first

2.在TOP N紀錄中抽出第M（M <= N）條記錄

ROWNUM是記錄表中資料編號的一個隱藏子段，所以可以在得到TOP N條記錄的時候同時抽出記錄的ROWNUM，然後再從這N條記錄中抽取記錄編號為M的記錄，即使我們希望得到的結果。

從上面的分析可以很容易得到下面的SQL語句。

SELECT 列名１．．．列名ｎ FROM

     (

     SELECT ROWNUM RECNO, 列名１．．．列名ｎFROM

       (SELECT 列名１．．．列名ｎ FROM 表名 ORDER BY 列名１．．．列名ｎ)

     WHERE ROWNUM <= N（抽出記錄數）

   ORDER BY ROWNUM ASC

     )

   WHERE RECNO = M（M <= N）

同樣以上表的資料為基礎，那麼得到以NAME的字母順排序的第二個顧客的資訊的SQL語句應該這樣寫：

   SELECT ID, NAME FROM

     (

      SELECT ROWNUM RECNO, ID, NAME FROM

        (SELECT * FROM CUSTOMER ORDER BY NAME)

         WHERE ROWNUM <= 3

         ORDER BY ROWNUM ASC )

       WHERE RECNO = 2

     結果則為：

   ID NAME

    05 fifth

3.抽出按某種方式排序的記錄集中的第N條記錄

   在2的說明中，當M = N的時候，即為我們的標題講的結果。實際上，2的做法在裡面N>M的部分的資料是基本上不會用到的，我們僅僅是為了說明方便而採用。

   如上所述，則SQL語句應為：

SELECT 列名１．．．列名ｎ FROM

     (

      SELECT ROWNUM RECNO, 列名１．．．列名ｎFROM

        (SELECT 列名１．．．列名ｎ FROM 表名 ORDER BY 列名１．．．列名ｎ)

         WHERE ROWNUM <= N（抽出記錄數）

      ORDER BY ROWNUM ASC

     )

     WHERE RECNO = N

     那麼，2中的例子的SQL語句則為：

    SELECT ID, NAME FROM

      (

       SELECT ROWNUM RECNO, ID, NAME FROM

         (SELECT * FROM CUSTOMER ORDER BY NAME)

       WHERE ROWNUM <= 2

       ORDER BY ROWNUM ASC

      )

      WHERE RECNO = 2

     結果為：

   ID NAME

    05 fifth

4.抽出按某種方式排序的記錄集中的第M條記錄開始的X條記錄

   3裡所講得僅僅是抽取一條記錄的情況，當我們需要抽取多條記錄的時候，此時在2中的N的取值應該是在N >= (M + X - 1)這個範圍內，當讓最經濟的取值就是取等好的時候了的時候了。當然最後的抽取條件也不是RECNO = N了，應該是RECNO BETWEEN M AND (M + X - 1)了，所以隨之而來的SQL語句則為：

   SELECT 列名１．．．列名ｎ FROM

    (

     SELECT ROWNUM RECNO, 列名１．．．列名ｎFROM

      (

      SELECT 列名１．．．列名ｎ FROM 表名 ORDER BY 列名１．．．列名ｎ)

      WHERE ROWNUM <= N （N >= (M + X - 1)）

    ORDER BY ROWNUM ASC

      )

     WHERE RECNO BETWEEN M AND (M + X - 1)

    同樣以上面的資料為例，則抽取NAME的字母順的第2條記錄開始的3條記錄的SQL語句為：

   SELECT ID, NAME FROM

     (

      SELECT ROWNUM RECNO, ID, NAME FROM

        (SELECT * FROM CUSTOMER ORDER BY NAME)

      WHERE ROWNUM <= (2 + 3 - 1)

      ORDER BY ROWNUM ASC

     )

     WHERE RECNO BETWEEN 2 AND (2 + 3 - 1)

     結果如下：

   ID NAME

    05 fifth

    01 first

   04 forth

    以此為基礎，再擴展的話，做成存儲過程，將開始記錄數以及抽取記錄數為引數，就可以輕鬆實現分頁抽取資料。

   當然了，上面所講的都是一些最基本的，實際應用中往往都沒有這麼簡單，但是不管怎麼說，不管複雜的應用總是由這些簡單的元素構成，掌握一些最基本的方法始終是重要的。