對于 Oracle 的 rownum 問題，很多資料都說不支持>,>=,=,between...and，只能用以上符號(<、<=、!=)，并非說用>,>=,=,between..and 時會提示SQL語法錯誤，而是經(jīng)常是查不出一條記錄來，還會出現(xiàn)似乎是莫名其妙的結(jié)果來，其實您只要理解好了這個 rownum 偽列的意義就不應(yīng)該感到驚奇。

rowid與rownum 雖都被稱為偽列，但它們的存在方式是不一樣的，rowid 可以說是物理存在的，表示記錄在表空間中的唯一位置ID，在DB中是唯一的。只要記錄沒被搬動過，rowid是不變的。rowid 相對于表來說又像表中的一般列，所以以 rowid 為條件就不會有rownum那些莫名其妙的結(jié)果出現(xiàn)。

rownum介紹：

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

對于下面的SQL語句

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

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

rownum>2沒有記錄,因為第一條不滿足去掉的話,第二條的rownum 又成了1,依此類推,所以永遠沒有滿足條件的記錄。或者可以這樣理解：rownum是一個序列,是oracle數(shù)據(jù)庫從數(shù)據(jù)文件或緩沖區(qū)中讀取數(shù)據(jù)的順序。它取得第一條記錄則rownum值為1,第二條為2,依次類推。如果你>,>=,=,between...and這些條件,因為從緩沖區(qū)或數(shù)據(jù)文件中得到的第一條記錄的rownum為1,不符合sql語句的條件,則被刪除,接著取下條，可是它的rownum還是1,又被刪除,依次類推,便沒有了數(shù)據(jù)。

有了以上從不同方面建立起來的對rownum的概念，那我們可以來認識使用rownum的幾種現(xiàn)象:

（1）select rownum,id,age,name from loaddata  where rownum != 10 為何是返回前9條數(shù)據(jù)呢?它與 select rownum,id,age,name from loaddata where rownum < 10返回的結(jié)果集是一樣的呢?
         因為是在查詢到結(jié)果集后,顯示完第9條記錄后,之后的記錄也都是 != 10或者 >=10,所以只顯示前面9條記錄。也可以這樣理解,rownum為9后記錄的rownum為10,因條件為 !=10,所以去掉,其后記錄補上,rownum又是10,也去掉,如果下去也就只會顯示前面9條記錄了。

（2）什么rownum >1時查不到一條記錄,而 rownum >0或rownum >=1卻總顯示所有記錄,這是因為rownum是在查詢到的結(jié)果集后加上去的，它總是從1開始。

（3）為什么between 1 and 10 或者 between 0 and 10 能查到結(jié)果,而用 between 2 and 10 卻得不到結(jié)果原因同上一樣，因為 rownum總是從1開始。

從上可得,任何時候想把rownum = 1這條記錄拋棄是不對的,它在結(jié)果集中是不可或缺的,少了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結(jié)果查不到數(shù)據(jù)。
因為rownum都是從1開始，但是1以上的自然數(shù)在rownum做等于判斷是時認為都是false條件，所以無法查到rownum = n（n>1的自然數(shù)）。
select rownum,id,age,name from loaddata where rownum = 1;（可以用在限制返回記錄條數(shù)的地方，保證不出錯，如：隱式游標(biāo)）

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對于大于某值的查詢條件

   如果想找到從第二行記錄以后的記錄，當(dāng)使用rownum>2是查不出記錄的，原因是由于rownum是一個總是從1開始的偽列，Oracle 認為rownum> n(n>1的自然數(shù))這種條件依舊不成立，所以查不到記錄。

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對于小于某值的查詢條件

如果想找到第三條記錄以前的記錄，當(dāng)使用rownum<3是能得到兩條記錄的。顯然rownum對于rownum<n（(n>1的自然數(shù)）的條件認為是成立的，所以可以找到記錄。
SQL> select rownum,id,age,name from loaddata where rownum < 3;

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

綜上幾種情況，可能有時候需要查詢rownum在某區(qū)間的數(shù)據(jù)，從上可以看出rownum對小于某值的查詢條件是人為true的，rownum對于大于某值的查詢條件直接認為是false的，但是可以間接的讓它轉(zhuǎn)為認為是true的。那就必須使用子查詢。例如要查詢rownum在第二行到第三行之間的數(shù)據(jù)，包括第二行和第三行數(shù)據(jù)，那么我們只能寫以下語句，先讓它返回小于等于三的記錄行，然后在主查詢中判斷新的rownum的別名列大于等于二的記錄行。但是這樣的操作會在大數(shù)據(jù)集中影響速度。

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

也可以用這種方法實現(xiàn):

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的是在取數(shù)據(jù)的時候產(chǎn)生的序號，所以想對指定排序的數(shù)據(jù)去指定的rowmun行數(shù)據(jù)就必須注意了。

前提條件：loaddata表中已經(jīng)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列來生成的序號。系統(tǒng)是按照記錄插入時的順序給記錄排的號，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標(biāo)出正確序號（有小到大），對于大數(shù)據(jù)量的時候，建議在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

實例

需求：假設(shè)不知道數(shù)據(jù)庫里的數(shù)據(jù)規(guī)則和數(shù)量,把所有的student數(shù)據(jù)打印到終端。

解：
rownum是偽列，在表里沒有，數(shù)據(jù)庫先是執(zhí)行from book遍歷book表，如果沒有where條件過濾，則先做成一個結(jié)果集，然后再看select后面的條件挑出合適的字段形成最后的結(jié)果集，如果有where條件，則不符合條件的就會從第一個結(jié)果集中刪除，后面的數(shù)據(jù)繼續(xù)加進來判斷，所以如果直接寫rownum=2，或者rownum>10這樣的語句就查不出數(shù)據(jù)，但是可以用一個子查詢來解決這個問題，對于select rownum,id from book where rownum=2;是查補出數(shù)據(jù)來的。

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;

 
轉(zhuǎn): http://blog.csdn.net/hailang99/  和    http://temptation.cnblogs.com/