轉：Oracle中的rownum不能使用大於>的問題

標題：Oracle中的rownum不能使用大於>的問題

轉自網路，稍做修改：

一、對rownum的說明

   關於Oracle 的 rownum 問題，很多資料都說不支援SQL語句中的“>、>=、=、between...and”運算子，只能用如下運算子號“<、<=、!=”，

   並非說用“>、>=、=、between..and”時會提示SQL語法錯誤，而是經常是查不出一條記錄來，還會出現似乎是莫名其妙的結果來。

   其實，只要理解好了這個 rownum 偽列的意義就不應該感到驚奇。

   rowid 與 rownum 雖都被稱為偽列，但它們的存在方式是不一樣的：

   rowid 是物理存在的，表示記錄在資料表空間中的唯一位置ID，在DB中是唯一的。只要記錄沒被搬動過，rowid是不變的。

   rowid 相對於表來說又像表中的一般列，所以，以 rowid 為條件就不會有rownum那些莫名其妙的結果出現。

   另外還要注意：rownum不能以任何基表的名稱作為首碼。

   對於下面的SQL語句

   SQL>select rownum,id,age,name from loaddata where rownum > 2;

    ROWNUM ID     AGE NAME
    ------- ------ --- ------

    rownum>2，沒有查詢到任何記錄。

    因為rownum總是從1開始的，第一條不滿足去掉的話，第二條的rownum 又成了1。依此類推，所以永遠沒有滿足條件的記錄。

    可以這樣理解：rownum是一個序列，是Oracle資料庫從資料檔案或緩衝區中讀取資料的順序。

    它取得第一條記錄則rownum值為1，第二條為2。依次類推。

    當使用“>、>=、=、between...and”這些條件時，從緩衝區或資料檔案中得到的第一條記錄的rownum為1，不符合sql語句的條件，會被刪除，接著取下條。

    下條的rownum還會是1，又被刪除，依次類推，便沒有了資料。

二、對rownum使用中幾種現象的分析說明
 
    有了以上從不同方面建立起來的對rownum的概念，下面認識使用rownum的幾種現象:

    （1） select rownum,id,age,name from loaddata where rownum != 10 為何是返回前9條資料呢？
     為什麼它與 select rownum,id,age,name from loaddata where rownum < 10 返回的結果集是一樣的？

     因為是在查詢到結果集後，顯示完第9條記錄後，之後的記錄都是 != 10或者 >=10，所以只顯示前面9條記錄。

     也可以這樣理解，rownum為9後，取的記錄的rownum為10，因條件為 !=10，所以刪掉。然後取下一條，其rownum又是10，也刪掉。以此類推。

     所以只會顯示前面9條記錄。

    （2）什麼rownum >1時查不到一條記錄，而 rownum >0或rownum >=1 卻總顯示所有記錄。

     這是因為rownum是在查詢到的結果集後，再加上去的，它總是從1開始的。

    （3）為什麼between 1 and 10 或者 between 0 and 10 能查到結果，而用 between 2 and 10 卻得不到結果。

     原因同上：因為 rownum總是從1開始。

     從上可得,任何時候想把rownum = 1這條記錄拋棄是不對的。它在結果集中是不可或缺的。

     少了rownum=1就像空中樓閣一般不能存在。所以，rownum條件要包含到1。

三、一些rownum實際運用的例子：

     -----------
     --sql建表指令碼

     create table LOADDATA
     (
         ID   VARCHAR2(50),
         AGE  VARCHAR2(50),
         NAME VARCHAR2(50)
     );
     -----------

    （1） rownum 對於“等於某值”的查詢條件

     如果希望找到loaddata表中第一條記錄的資訊，可以使用rownum=1作為條件。

     但是想找到loaddata表中第二條記錄的資訊，使用rownum=2，是查不到資料的。

     因為rownum都是從“1”開始。

     “1”以上的自然數，在rownum做等於判斷是時認為都是false條件，所以無法查到rownum = n（n>1的自然數）。

      select rownum,id,age,name
      from loaddata
      where rownum = 1;   --可以用在限制返回記錄條數的地方，保證不出錯，如：隱式遊標。

    SQL>select rownum,id,age,name from loaddata where rownum = 1;

    ROWNUM ID     AGE NAME
    ------- ------ --- ------
         1 200001 22   AAA

    SQL>select rownum,id,age,name from loaddata where rownum = 2;

    ROWNUM ID     AGE NAME
    ------- ------ --- ------

   註：SQL>select rownum,id,age,name from loaddata where rownum != 3; --返回的是前2條記錄。

     ROWNUM ID     AGE NAME
    ------- ------ --- ------
         1 200001 22   AAA
         2 200002 22   BBB

  （2）rownum對於大於某值的查詢條件

    如果想找到從第二行記錄以後的記錄，當使用rownum>2是查不出記錄的。

    原因是由於rownum是一個總是從1開始的偽列，Oracle 認為rownum> n(n>1的自然數)這種條件依舊不成立，所以查不到記錄。

    SQL>select rownum,id,age,name from loaddata where rownum > 2;

    ROWNUM ID     AGE NAME
    ------- ------ --- ------

    那如何才能找到第二行以後的記錄？

    可以使用下面的子查詢方法來解決。

    注意子查詢中的rownum必須要有別名，否則仍然會查不到記錄，這是因為rownum不是某個表的列。

    如果不起別名的話，無法知道rownum是子查詢的列，還是主查詢的列。

     SQL>select rownum,id,age,name from(select rownum no ,id,age,name from loaddata) where no > 2;

     ROWNUM ID     AGE NAME
     ------- ------ --- ------
         3 200003 22   CCC
         4 200004 22   DDD
         5 200005 22   EEE
         6 200006 22   AAA

     SQL>select * from(select rownum,id,age,name from loaddata) where rownum > 2;

     ROWNUM ID     AGE NAME
     ------- ------ --- ------

 

    （3）rownum對於小於某值的查詢條件

     如果想找到第三條記錄以前的記錄，當使用rownum<3是能得到兩條記錄的。

     顯然rownum對於rownum<n（(n>1的自然數）的條件認為是成立的，所以可以找到記錄。

     SQL> select rownum,id,age,name from loaddata where rownum < 3;

     ROWNUM ID     AGE NAME
     ------- ------ --- ------
         1 200001 22   AAA
         2 200002 22 BBB

     綜上幾種情況：

     可能有時候需要查詢rownum在某區間的資料，從上可以看出rownum對小於某值的查詢條件是認為true的。

     rownum對於大於某值的查詢條件直接認為是false的，但是可以間接的讓它轉為認為是true的，那就必須使用子查詢。

     例如要查詢rownum在第二行到第三行之間的資料，包括第二行和第三行資料，那麼只能寫以下語句，先讓它返回小於等於三的記錄行，

     然後在主查詢中判斷新的rownum的“別名列”大於等於二的記錄行。但是這樣的操作會在大資料集中影響到檢索速度。

     SQL>select * from (select rownum no,id,age,name from loaddata where rownum <= 3 ) where no >= 2; --必須是裡小外大

     ROWNUM ID     AGE NAME
     ------- ------ --- ------
         2 200002 22 BBB
         3 200003 22   CCC

     也可以用這種方法實現:

     SQL>select rownum,id,age,name from loaddata where rownum < 4
         minus
         select rownum,id,age,name from loaddata where rownum < 2

     ROWNUM ID     AGE NAME
     ------- ------ --- ------
         2 200002 22 BBB
         3 200003 22   CCC

 

    （4）rownum和排序

     Oracle中的rownum的是在取資料的時候產生的序號。故，如在已排序的資料中，要求取出指定的rowmun行資料時，就需注意了。

     前提條件：loaddata表中已經insert了5條記錄，最後一條記錄id是200005，接著insert into loaddata values('200006','22','AAA');

     SQL>select rownum,id,age,name from loaddata;

     ROWNUM ID     AGE NAME
     ------- ------ --- ------
         1 200001 22   AAA
         2 200002 22   BBB
         3 200003 22   CCC
         4 200004 22   DDD
         5 200005 22   EEE
         6 200006 22   AAA

     SQL>select rownum ,id,age,name from loaddata order by name;

     ROWNUM ID     AGE NAME
     ------- ------ --- ------
         1 200001 22   AAA
         6 200006 22   AAA
         2 200002 22   BBB
         3 200003 22   CCC
         4 200004 22   DDD
         5 200005 22   EEE

     可以看出，rownum並不是按照name列來產生的序號。

     系統是按照記錄插入時的順序給記錄排的號，rowid也是順序分配的。

     為瞭解決這個問題，必須使用子查詢

     SQL>select rownum ,id,age,name from (select * from loaddata order by name);

     ROWNUM ID     AGE NAME
     ------- ------ --- ------
         1 200001 22   AAA
         2 200006 22   AAA
         3 200002 22   BBB
         4 200003 22   CCC
         5 200004 22   DDD
         6 200005 22   EEE

     這樣就成了按name排序，並且用rownum標出正確序號（有小到大）。

     對於大資料量的時候，建議在order by 的欄位上加主鍵或索引這樣效率會提高很多。

     同樣，返回中間的記錄集：

     SQL>select * from ( select rownum ro,id,age,name from loaddata where rownum < 5 order by name ) where ro > 2; (先選再排序再選)

     ROWNUM ID     AGE NAME
     ------- ------ --- ------
         3 200002 22   BBB
         4 200003 22   CCC

     一般業務需求中，是需要先排序後，再返回中間記錄集：

     SQL>select * from (select t.*,rownum ro from (select id,age,name from loaddata order by name ) t where rownum < 5 ) where ro>=2; (先排序再選再選)

     ROWNUM ID     AGE NAME
     ------- ------ --- ------
         3 200002 22   BBB
         4 200003 22   CCC

     注意此時的SQL語句寫法，使用了多重（三層）嵌套。同時注意：rownum使用了“列別名”。
 
     實際上，該語句也是Oracle資料集一個經典的SQL語句分頁演算法：先排序，再選擇rownum < 某頁的最大值，再選擇rownum > 某頁的最小值。

 

四、一個執行個體：

    需求：假設不知道資料庫裡的資料規則和數量，要把所有的student資料列印到終端。

    解：rownum是偽列，在表裡沒有，資料庫先是執行from book遍曆book表。

        如果沒有where條件過濾，則先做成一個結果集，然後再看select後面的條件，挑出合適的欄位形成最後的結果集。

        如果有 where條件，則不合格就會從第一個結果集中刪除，後面的資料繼續加進來判斷。

        所以如果直接寫rownum=2，或者rownum>10這樣的語句就查不出資料。

        可以用一個子查詢來解決這個問題：對於select rownum,id from book where rownum=2; 是查不出資料來的。

       declare
           v_number binary_integer;
           v_student student%rowtype;
       begin
           select count(*) into v_number from student;
           for i in 1..v_number loop
              select id,name,age into v_student from ( select rownum rn,id,name,age from student ) where rn=i;
              dbms_output.put_line('id: '||v_student.id||' name:'||v_student.name);
          end loop;
       end;

      rownum是對結果集加的一個偽列，即先查到結果集之後再加上去的一個列 (強調：先要有結果集)。

      簡單的說 rownum 是對符合條件結果的序號。

      它總是從1開始排起的。所以，選出的結果不可能沒有1，反而有其他大於1的值。

[－完－]

                                      JRQ  2011/08/03