注意：rownum從1開(kāi)始；
rownum按照記錄插入時(shí)的順序給記錄排序，所以有order by的子句時(shí)一定要注意??！
使用時(shí)rownum，order by字段是否為主鍵有什么影響？
子查詢(xún)中rownum rn，而rn用到外查詢(xún)中到底是怎樣的序列？
若id主鍵是按照從小到大的順序插入的，select語(yǔ)句沒(méi)有g(shù)roup by 和order by的子句時(shí)，rownum的順序和id順序基本一致。
---------------------------------------------------------------------------------------------------------------
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1812603
對于 Oracle 的 rownum 問(wèn)題，很多資料都說(shuō)不支持>,>=,=,between...and，只能用以上符號(<、<=、!=)，并非說(shuō)用>,>=,=,between..and 時(shí)會(huì )提示SQL語(yǔ)法錯誤，而是經(jīng)常是查不出一條記錄來(lái)，還會(huì )出現似乎是莫名其妙的結果來(lái)，其實(shí)您只要理解好了這個(gè) rownum 偽列的意義就不應該感到驚奇，同樣是偽列，rownum 與 rowid 可有些不一樣，下面以例子說(shuō)明
假設某個(gè)表 t1(c1) 有 20 條記錄
如果用 select rownum,c1 from t1 where rownum < 10, 只要是用小于號，查出來(lái)的結果很容易地與一般理解在概念上能達成一致，應該不會(huì )有任何疑問(wèn)的。
可如果用 select rownum,c1 from t1 where rownum > 10 (如果寫(xiě)下這樣的查詢(xún)語(yǔ)句，這時(shí)候在您的頭腦中應該是想得到表中后面10條記錄)，你就會(huì )發(fā)現，顯示出來(lái)的結果要讓您失望了，也許您還會(huì )懷疑是不誰(shuí)刪了一些記錄，然后查看記錄數，仍然是 20 條??？那問(wèn)題是出在哪呢？
先好好理解 rownum 的意義吧。因為ROWNUM是對結果集加的一個(gè)偽列，即先查到結果集之后再加上去的一個(gè)列 (強調：先要有結果集)。簡(jiǎn)單的說(shuō) rownum 是對符合條件結果的序列號。它總是從1開(kāi)始排起的。所以你選出的結果不可能沒(méi)有1，而有其他大于1的值。所以您沒(méi)辦法期望得到下面的結果集：
11 aaaaaaaa
12 bbbbbbb
13 ccccccc
.................
rownum >10 沒(méi)有記錄，因為第一條不滿(mǎn)足去掉的話(huà)，第二條的ROWNUM又成了1，所以永遠沒(méi)有滿(mǎn)足條件的記錄。或者可以這樣理解：
ROWNUM是一個(gè)序列，是oracle數據庫從數據文件或緩沖區中讀取數據的順序。它取得第一條記錄則rownum值為1，第二條為2，依次類(lèi)推。如果你用>,>=,=,between...and這些條件，因為從緩沖區或數據文件中得到的第一條記錄的rownum為1，則被刪除，接著(zhù)取下條，可是它的rownum還是1，又被刪除，依次類(lèi)推，便沒(méi)有了數據。
有了以上從不同方面建立起來(lái)的對 rownum 的概念，那我們可以來(lái)認識使用 rownum 的幾種現像
1. select rownum,c1 from t1 where rownum != 10 為何是返回前9條數據呢？它與 select rownum,c1 from tablename where rownum < 10 返回的結果集是一樣的呢？
因為是在查詢(xún)到結果集后，顯示完第 9 條記錄后，之后的記錄也都是 != 10,或者 >=10,所以只顯示前面9條記錄。也可以這樣理解，rownum 為9后的記錄的 rownum為10，因條件為 !=10，所以去掉，其后記錄補上，rownum又是10，也去掉，如果下去也就只會(huì )顯示前面9條記錄了。
2. 為什么 rownum >1 時(shí)查不到一條記錄，而 rownum >0 或 rownum >=1 卻總顯示所有的記錄？
因為 rownum 是在查詢(xún)到的結果集后加上去的，它總是從1開(kāi)始。
3. 為什么 between 1 and 10 或者 between 0 and 10 能查到結果，而用 between 2 and 10 卻得不到結果？
原因同上一樣，因為 rownum 總是從 1 開(kāi)始。從上可以看出，任何時(shí)候想把 rownum = 1 這條記錄拋棄是不對的，它在結果集中是不可或缺的，少了rownum=1 就像空中樓閣一般不能存在，所以你的 rownum 條件要包含到 1 。
但如果就是想要用 rownum > 10 這種條件的話(huà)話(huà)就要用嵌套語(yǔ)句,把 rownum 先生成，然后對他進(jìn)行查詢(xún)。
select *
from (selet rownum as rn，t1.* from a where ...)
where rn >10
一般代碼中對結果集進(jìn)行分頁(yè)就是這么干的。
另外：rowid 與 rownum 雖都被稱(chēng)為偽列，但它們的存在方式是不一樣的，rowid 可以說(shuō)是物理存在的，表示記錄在表空間中的唯一位置ID，在DB中唯一。只要記錄沒(méi)被搬動(dòng)過(guò)，rowid是不變的。rowid 相對于表來(lái)說(shuō)又像表中的一般列，所以以 rowid 為條件就不會(huì )有 rownum那些情況發(fā)生。
另外還要注意：rownum不能以任何基表的名稱(chēng)作為前綴。
以下摘自《解析oracle的rownum》作者：chen_liang
對于rownum來(lái)說(shuō)它是oracle系統順序分配為從查詢(xún)返回的行的編號，返回的第一行分配的是1，第二行是2，依此類(lèi)推，這個(gè)偽字段可以用于限制查詢(xún)返回的總行數，且rownum不能以任何表的名稱(chēng)作為前綴。
(1) rownum 對于等于某值的查詢(xún)條件
如果希望找到學(xué)生表中第一條學(xué)生的信息，可以使用rownum=1作為條件。但是想找到學(xué)生表中第二條學(xué)生的信息，使用rownum=2結果查不到數據。因為rownum都是從1開(kāi)始，但是1以上的自然數在rownum做等于判斷是時(shí)認為都是false條件，所以無(wú)法查到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對于大于某值的查詢(xún)條件
如果想找到從第二行記錄以后的記錄，當使用rownum>2是查不出記錄的，原因是由于rownum是一個(gè)總是從1開(kāi)始的偽列，Oracle 認為rownum> n(n>1的自然數)這種條件依舊不成立，所以查不到記錄。
查找到第二行以后的記錄可使用以下的子查詢(xún)方法來(lái)解決。注意子查詢(xún)中的rownum必須要有別名，否則還是不會(huì )查出記錄來(lái)，這是因為rownum不是某個(gè)表的列，如果不起別名的話(huà)，無(wú)法知道rownum是子查詢(xún)的列還是主查詢(xún)的列。
SQL>select * from(select rownum no ,id,name from student) where no>2;
NO ID NAME
---------- ------ ---------------------------------------------------
3 200003 李三
4 200004 趙四
（3）rownum對于小于某值的查詢(xún)條件
rownum對于rownum<n（(n>1的自然數）的條件認為是成立的，所以可以找到記錄。
SQL> select rownum,id,name from student where rownum <3;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
1 200001 張一
2 200002 王二
查詢(xún)r(jià)ownum在某區間的數據，必須使用子查詢(xún)。例如要查詢(xún)r(jià)ownum在第二行到第三行之間的數據，包括第二行和第三行數據，那么我們只能寫(xiě)以下語(yǔ)句，先讓它返回小于等于三的記錄行，然后在主查詢(xún)中判斷新的rownum的別名列大于等于二的記錄行。但是這樣的操作會(huì )在大數據集中影響速度。
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的是在取數據的時(shí)候產(chǎn)生的序號，所以想對指定排序的數據去指定的rowmun行數據就必須注意了。
SQL> select rownum ,id,name from student order by name;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
3 200003 李三
2 200002 王二
1 200001 張一
4 200004 趙四
可以看出，rownum并不是按照name列來(lái)生成的序號。系統是按照記錄插入時(shí)的順序給記錄排的號，rowid也是順序分配的。為了解決這個(gè)問(wèn)題，必須使用子查詢(xún)；
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標出正確序號（有小到大）
筆者在工作中有一上百萬(wàn)條記錄的表，在jsp頁(yè)面中需對該表進(jìn)行分頁(yè)顯示，便考慮用rownum來(lái)作，下面是具體方法(每頁(yè)顯示20條)：
“select * from tabname where rownum<20 order by name" 但卻發(fā)現oracle卻不能按自己的意愿來(lái)執行，而是先隨便取20條記錄，然后再order by，后經(jīng)咨詢(xún)oracle,說(shuō)rownum確實(shí)就這樣，想用的話(huà)，只能用子查詢(xún)來(lái)實(shí)現先排序，后rownum，方法如下：
"select * from (select * from tabname order by name) where rownum<20",但這樣一來(lái)，效率會(huì )低很多。
后經(jīng)筆者試驗，只需在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是一個(gè)總是從1開(kāi)始的偽列，Oracle 認為這種條件不成立。
另外，這個(gè)方法更快：
select * from (
select rownum r,a from yourtable
where rownum <= 20
order by name )
where r > 10
這樣取出第11-20條記錄!(先選再排序再選)
要先排序再選則須用select嵌套：內層排序外層選。
rownum是隨著(zhù)結果集生成的，一旦生成，就不會(huì )變化了；同時(shí),生成的結果是依次遞加的，沒(méi)有1就永遠不會(huì )有2!
rownum 是在查詢(xún)集合產(chǎn)生的過(guò)程中產(chǎn)生的偽列，并且如果where條件中存在 rownum 條件的話(huà)，則:
1： 假如判定條件是常量，則：
只能 rownum = 1, <= 大于1 的自然數， = 大于1 的數是沒(méi)有結果的；大于一個(gè)數也是沒(méi)有結果的
即 當出現一個(gè) rownum 不滿(mǎn)足條件的時(shí)候則 查詢(xún)結束 this is stop key（一個(gè)不滿(mǎn)足，系統將該記錄過(guò)濾掉，則下一條記錄的rownum還是這個(gè)，所以后面的就不再有滿(mǎn)足記錄，this is stop key）；
2： 假如判定值不是常量，則：
若條件是 = var , 則只有當 var 為1 的時(shí)候才滿(mǎn)足條件，這個(gè)時(shí)候不存在 stop key ,必須進(jìn)行full scan ,對每個(gè)滿(mǎn)足其他where條件的數據進(jìn)行判定，選出一行后才能去選rownum=2的行……
以下摘自《中國IT實(shí)驗室》
1.在ORACLE中實(shí)現SELECT TOP N
由于ORACLE不支持SELECT TOP語(yǔ)句，所以在ORACLE中經(jīng)常是用ORDER BY跟ROWNUM的組合來(lái)實(shí)現SELECT TOP N的查詢(xún)。
簡(jiǎn)單地說(shuō)，實(shí)現方法如下所示：
SELECT 列名１．．．列名ｎ FROM
(SELECT 列名１．．．列名ｎ FROM 表名 ORDER BY 列名１．．．列名ｎ)
WHERE ROWNUM <= N（抽出記錄數）
ORDER BY ROWNUM ASC
下面舉個(gè)例子簡(jiǎn)單說(shuō)明一下。
顧客表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的字母順抽出前三個(gè)顧客的SQL語(yǔ)句如下所示：
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是記錄表中數據編號的一個(gè)隱藏子段，所以可以在得到TOP N條記錄的時(shí)候同時(shí)抽出記錄的ROWNUM，然后再從這N條記錄中抽取記錄編號為M的記錄，即使我們希望得到的結果。
從上面的分析可以很容易得到下面的SQL語(yǔ)句。
SELECT 列名１．．．列名ｎ FROM
(
SELECT ROWNUM RECNO, 列名１．．．列名ｎFROM
(SELECT 列名１．．．列名ｎ FROM 表名 ORDER BY 列名１．．．列名ｎ)
WHERE ROWNUM <= N（抽出記錄數）
ORDER BY ROWNUM ASC
)
WHERE RECNO = M（M <= N）
同樣以上表的數據為基礎，那么得到以NAME的字母順排序的第二個(gè)顧客的信息的SQL語(yǔ)句應該這樣寫(xiě)：
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的說(shuō)明中，當M = N的時(shí)候，即為我們的標題講的結果。實(shí)際上，2的做法在里面N>M的部分的數據是基本上不會(huì )用到的，我們僅僅是為了說(shuō)明方便而采用。
如上所述，則SQL語(yǔ)句應為：
SELECT 列名１．．．列名ｎ FROM
(
SELECT ROWNUM RECNO, 列名１．．．列名ｎFROM
(SELECT 列名１．．．列名ｎ FROM 表名 ORDER BY 列名１．．．列名ｎ)
WHERE ROWNUM <= N（抽出記錄數）
ORDER BY ROWNUM ASC
)
WHERE RECNO = N
那么，2中的例子的SQL語(yǔ)句則為：
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條記錄開(kāi)始的X條記錄
3里所講得僅僅是抽取一條記錄的情況，當我們需要抽取多條記錄的時(shí)候，此時(shí)在2中的N的取值應該是在N >= (M + X - 1)這個(gè)范圍內，當讓最經(jīng)濟的取值就是取等好的時(shí)候了的時(shí)候了。當然最后的抽取條件也不是RECNO = N了，應該是RECNO BETWEEN M AND (M + X - 1)了，所以隨之而來(lái)的SQL語(yǔ)句則為：
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條記錄開(kāi)始的3條記錄的SQL語(yǔ)句為：
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
以此為基礎，再擴展的話(huà)，做成存儲過(guò)程，將開(kāi)始記錄數以及抽取記錄數為參數，就可以輕松實(shí)現分頁(yè)抽取數據。
當然了，上面所講的都是一些最基本的，實(shí)際應用中往往都沒(méi)有這么簡(jiǎn)單，但是不管怎么說(shuō)，不管復雜的應用總是由這些簡(jiǎn)單的元素構成，掌握一些最基本的方法始終是重要的。
