Oracle Rownum 偽列詳解

【使用情境】有很多同事問過我偽列的事情，主要想在匯出資料的時候添加一些序號==，其實偽列還有很多作用。

官方參考文檔：Oracle Database

SQL Language Reference

11g Release 2 (11.2)

E17118-04

ROWID Pseudocolumn& ROWNUM Pseudocolumn

以下內容整理自互連網：

Rowid 與 Rownum 雖都被稱為偽列，但它們的存在方式是不一樣的，rowid 可以說是物理存在的，表示記錄在資料表空間中的唯一位置ID，在DB中唯一。只要記錄沒被搬動過，rowid是不變的(如果對錶進行shrink或者move rowid就會改變)。rowid 相對於表來說又像表中的一般列，所以以 rowid 為條件就不會有 rownum那些情況發生。 rownum不能以任何基表的名稱作為首碼。

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

舉例說明：
例如表：student(學生)表，表結構為：

CREATE TABLE student (ID CHAR (6), name VARCHAR2 (100));

INSERT INTO student

VALUES ('200001', '張一');

INSERT INTO student

VALUES ('200002', '王二');

INSERT INTO student

VALUES ('200003', '李三');

INSERT INTO student

VALUES ('200004', '趙四');

COMMIT;

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;

ROWNUM ID NAME

---------------- ----------- --------------------------

1 200001 張一

可以用在限制返回記錄條數的地方，保證不出錯，如：隱式遊標

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

未選定行SQL> select rownum,id,name from student where rownum=1;（可以用在限制返回記錄條數的地方，保證不出錯，如：隱式遊標）

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

ROWNUM ID NAME

---------- ------ ---------------------------------------------------

1 200001 張一

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

ROWNUM ID NAME

---------- ------ ---------------------------------------------------

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

SQL> select rownum,id,name from student where rownum >2;

未選定行SQL> select rownum,id,name from student where rownum >2;

ROWNUM ID NAME

---------- ------ ---------------------------------------------------
那如何才能找到第二行以後的記錄呀。可以使用以下的子查詢方法來解決。注意子查詢中的rownum必須要有別名，否則還是不會查出記錄來，這是因為rownum不是某個表的列，如果不起別名的話，無法知道rownum是子查詢的列還是主查詢的列。

SQL> select * from(select rownum no ,id,name from student) where no>2;

NO ID NAME

---------- ------ ------------------------------------------------------

3 200003 李三

4 200004 趙四

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

未選定行SQL>select * from(select rownum no ,id,name from student) where no>2;

NO ID NAME

---------- ------ ---------------------------------------------------

3 200003 李三4 200004 趙四

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

ROWNUM ID NAME

---------- ------ ---------------------------------------------------
3. rownum對於小於某值的查詢條件
如果想找到第三條記錄以前的記錄，當使用rownum<3是能得到兩條記錄的。顯然rownum對於rownum<n（(n>1的自然數）的條件認為是成立的，所以可以找到記錄。

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

ROWNUM ID NAME

---------- ------ ---------------------------------------

1 200001 張一

2 200002 王二SQL> select rownum,id,name from student where rownum <3;

ROWNUM ID NAME

---------- ------ ---------------------------------------------------

1 200001 張一

2 200002 王二
綜上幾種情況，可能有時候需要查詢rownum在某區間的資料，那怎麼辦呀從上可以看出rownum對小於某值的查詢條件是人為true的，rownum對於大於某值的查詢條件直接認為是false的，但是可以間接的讓它轉為認為是true的。那就必須使用子查詢。例如要查詢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 李三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 趙四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代碼

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

ROWNUM ID NAME

---------- ------ ---------------------------------------------------

1 200003 李三

2 200002 王二

3 200001 張一

4 200004 趙四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

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

select * from (select * from tabname order by name) where rownum<20

但這樣一來，效率會較低很多。
後經實驗，只需在order by 的欄位上加主鍵或索引即可讓oracle先按 該欄位排序，然後再rownum；方法不變：

select * from tabname where rownum<20 order by name

select * from tabname where rownum<20 order by name

5. 取得某列中第N大的行

select column_name from

(select table_name.*,dense_rank() over (order by column desc) rank from table_name)

where rank = &N；

select column_name from

(select table_name.*,dense_rank() over (order by column desc) rank from table_name)

where rank = &N；

6. 假如要返回前5條記錄：

select * from tablename where rownum<6;( rownum <= 5 或是rownum != 6)

select * from tablename where rownum<6;(或是rownum <= 5 或是rownum != 6)

7. 假如要返回第5-9條記錄：

select * from tablename

where …

and rownum<10

minus

select * from tablename

where …

and rownum<5

order by name

select * from tablenamewhere …and rownum<10minusselect * from tablenamewhere …

and rownum<5

order by name

選出結果後用name排序顯示結果。(先選再排序)

另外，這個方法更快：

select * from (

select rownum r,a from yourtable

where rownum <= 20

order by name )

where r > 10

select * from (select rownum r,a from yourtablewhere rownum <= 20order by name )

where r > 10

這樣取出第11-20條記錄!(先選再排序再選)

註：minus， 用集合的觀點來看是差集，如：
select id from table1
minus
select id from table2
意思是：id在表table1中，並且不在表Table2中的記錄

注意：
1. 只能用以上符號(<、<=、!=)。
2. select * from tablename where rownum != 10; 返回的是前９條記錄。
3. 不能用：>,>=,=,Between...and。由於rownum是一個總是從1開始的偽列，Oracle 認為這種條件 不成立。 這裡的不能用表現在 查不到記錄.
4.要先排序再選則須用select嵌套：內層排序外層選。
5.rownum是隨著結果集產生的，一旦產生，就不會變化了；同時,產生的結果是依次遞加的，沒有1就永遠不會有2!
6.rownum 是在 查詢集合產生的過程中產生的偽列，並且如果where條件中存在 rownum 條件的話，則:

6.1： 假如 判定條件是常量，則：
只能 rownum = 1, <= 大於1 的自然數， = 大於1 的數是沒有結果的， 大於一個數也是沒有結果的
即 當出現一個 rownum 不滿足條件的時候則 查詢結束,this is stop key!

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