解析oracle的rownum
(Oracle）rownum用法詳解
對(duì)于rownum來(lái)說(shuō)它是oracle系統(tǒng)順序分配為從查詢返回的行的編號(hào)，返回的第一行分配的是1，第二行是2，依此類推，這個(gè)偽字段可以用于限制查詢返回的總行數(shù)，且rownum不能以任何表的名稱作為前綴。
(1) rownum 對(duì)于等于某值的查詢條件
如果希望找到學(xué)生表中第一條學(xué)生的信息，可以使用rownum=1作為條件。但是想找到學(xué)生表中第二條學(xué)生的信息，使用rownum=2結(jié)果查不到數(shù)據(jù)。因?yàn)閞ownum都是從1開(kāi)始，但是1以上的自然數(shù)在rownum做等于判斷是時(shí)認(rèn)為都是false條件，所以無(wú)法查到rownum = n（n>1的自然數(shù)）。
SQL> select rownum,id,name from student where rownum=1;（可以用在限制返回記錄條數(shù)的地方，保證不出錯(cuò)，如：隱式游標(biāo)）
SQL> select rownum,id,name from student where rownum =2;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
（2）rownum對(duì)于大于某值的查詢條件
如果想找到從第二行記錄以后的記錄，當(dāng)使用rownum>2是查不出記錄的，原因是由于rownum是一個(gè)總是從1開(kāi)始的偽列，Oracle 認(rèn)為rownum> n(n>1的自然數(shù))這種條件依舊不成立，所以查不到記錄。
查找到第二行以后的記錄可使用以下的子查詢方法來(lái)解決。注意子查詢中的rownum必須要有別名，否則還是不會(huì)查出記錄來(lái)，這是因?yàn)閞ownum不是某個(gè)表的列，如果不起別名的話，無(wú)法知道rownum是子查詢的列還是主查詢的列。
SQL>select * from(select rownum no ,id,name from student) where no>2;
NO ID NAME
---------- ------ ---------------------------------------------------
3 200003 李三
4 200004 趙四
（3）rownum對(duì)于小于某值的查詢條件
rownum對(duì)于rownum<n（(n>1的自然數(shù)）的條件認(rèn)為是成立的，所以可以找到記錄。
SQL> select rownum,id,name from student where rownum <3;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
1 200001 張一
2 200002 王二
查詢r(jià)ownum在某區(qū)間的數(shù)據(jù)，必須使用子查詢。例如要查詢r(jià)ownum在第二行到第三行之間的數(shù)據(jù)，包括第二行和第三行數(shù)據(jù)，那么我們只能寫(xiě)以下語(yǔ)句，先讓它返回小于等于三的記錄行，然后在主查詢中判斷新的rownum的別名列大于等于二的記錄行。但是這樣的操作會(huì)在大數(shù)據(jù)集中影響速度。
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ù)據(jù)的時(shí)候產(chǎn)生的序號(hào)，所以想對(duì)指定排序的數(shù)據(jù)去指定的rowmun行數(shù)據(jù)就必須注意了。
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)生成的序號(hào)。系統(tǒng)是按照記錄插入時(shí)的順序給記錄排的號(hào)，rowid也是順序分配的。為了解決這個(gè)問(wè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標(biāo)出正確序號(hào)（有小到大）
筆者在工作中有一上百萬(wàn)條記錄的表，在jsp頁(yè)面中需對(duì)該表進(jìn)行分頁(yè)顯示，便考慮用rownum來(lái)作，下面是具體方法(每頁(yè)顯示20條)：
“select * from tabname where rownum<20 order by name" 但卻發(fā)現(xiàn)oracle卻不能按自己的意愿來(lái)執(zhí)行，而是先隨便取20條記錄，然后再order by，后經(jīng)咨詢oracle,說(shuō)rownum確實(shí)就這樣，想用的話，只能用子查詢來(lái)實(shí)現(xiàn)先排序，后rownum，方法如下：
"select * from (select * from tabname order by name) where rownum<20",但這樣一來(lái)，效率會(huì)低很多。
后經(jīng)筆者試驗(yàn)，只需在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
選出結(jié)果后用name排序顯示結(jié)果。(先選再排序)
注意：只能用以上符號(hào)(<、<=、!=)。
select * from tablename where rownum != 10;返回的是前９條記錄。
不能用：>,>=,=,Between...and。由于rownum是一個(gè)總是從1開(kāi)始的偽列，Oracle 認(rèn)為這種條件不成立。
另外，這個(gè)方法更快：
select * from (
select rownum r,a from yourtable
where rownum <= 20
order by name )
where r > 10
這樣取出第11-20條記錄!(先選再排序再選)
要先排序再選則須用select嵌套：內(nèi)層排序外層選。
rownum是隨著結(jié)果集生成的，一旦生成，就不會(huì)變化了；同時(shí),生成的結(jié)果是依次遞加的，沒(méi)有1就永遠(yuǎn)不會(huì)有2!
rownum 是在查詢集合產(chǎn)生的過(guò)程中產(chǎn)生的偽列，并且如果where條件中存在 rownum 條件的話，則:
1： 假如判定條件是常量，則：
只能 rownum = 1, <= 大于1 的自然數(shù)， = 大于1 的數(shù)是沒(méi)有結(jié)果的；大于一個(gè)數(shù)也是沒(méi)有結(jié)果的
即 當(dāng)出現(xiàn)一個(gè) rownum 不滿足條件的時(shí)候則 查詢結(jié)束 this is stop key（一個(gè)不滿足，系統(tǒng)將該記錄過(guò)濾掉，則下一條記錄的rownum還是這個(gè)，所以后面的就不再有滿足記錄，this is stop key）；
2： 假如判定值不是常量，則：
若條件是 = var , 則只有當(dāng) var 為1 的時(shí)候才滿足條件，這個(gè)時(shí)候不存在 stop key ,必須進(jìn)行full scan ,對(duì)每個(gè)滿足其他where條件的數(shù)據(jù)進(jìn)行判定，選出一行后才能去選rownum=2的行……
以下摘自《中國(guó)IT實(shí)驗(yàn)室》
1.在ORACLE中實(shí)現(xiàn)SELECT TOP N
由于ORACLE不支持SELECT TOP語(yǔ)句，所以在ORACLE中經(jīng)常是用ORDER BY跟ROWNUM的組合來(lái)實(shí)現(xiàn)SELECT TOP N的查詢。
簡(jiǎn)單地說(shuō)，實(shí)現(xiàn)方法如下所示：
SELECT 列名１．．．列名ｎ FROM
(SELECT 列名１．．．列名ｎ FROM 表名 ORDER BY 列名１．．．列名ｎ)
WHERE ROWNUM <= N（抽出記錄數(shù)）
ORDER BY ROWNUM ASC
下面舉個(gè)例子簡(jiǎn)單說(shuō)明一下。
顧客表customer(id,name)有如下數(shù)據(jù)：
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
輸出結(jié)果為：
ID NAME
08 eighth
05 fifth
01 first
2.在TOP N紀(jì)錄中抽出第M（M <= N）條記錄
ROWNUM是記錄表中數(shù)據(jù)編號(hào)的一個(gè)隱藏子段，所以可以在得到TOP N條記錄的時(shí)候同時(shí)抽出記錄的ROWNUM，然后再?gòu)倪@N條記錄中抽取記錄編號(hào)為M的記錄，即使我們希望得到的結(jié)果。
從上面的分析可以很容易得到下面的SQL語(yǔ)句。
SELECT 列名１．．．列名ｎ FROM
(
SELECT ROWNUM RECNO, 列名１．．．列名ｎFROM
(SELECT 列名１．．．列名ｎ FROM 表名 ORDER BY 列名１．．．列名ｎ)
WHERE ROWNUM <= N（抽出記錄數(shù)）
ORDER BY ROWNUM ASC
)
WHERE RECNO = M（M <= N）
同樣以上表的數(shù)據(jù)為基礎(chǔ)，那么得到以NAME的字母順排序的第二個(gè)顧客的信息的SQL語(yǔ)句應(yīng)該這樣寫(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
結(jié)果則為：
ID NAME
05 fifth
3.抽出按某種方式排序的記錄集中的第N條記錄
在2的說(shuō)明中，當(dāng)M = N的時(shí)候，即為我們的標(biāo)題講的結(jié)果。實(shí)際上，2的做法在里面N>M的部分的數(shù)據(jù)是基本上不會(huì)用到的，我們僅僅是為了說(shuō)明方便而采用。
如上所述，則SQL語(yǔ)句應(yīng)為：
SELECT 列名１．．．列名ｎ FROM
(
SELECT ROWNUM RECNO, 列名１．．．列名ｎFROM
(SELECT 列名１．．．列名ｎ FROM 表名 ORDER BY 列名１．．．列名ｎ)
WHERE ROWNUM <= N（抽出記錄數(shù)）
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
結(jié)果為：
ID NAME
05 fifth
4.抽出按某種方式排序的記錄集中的第M條記錄開(kāi)始的X條記錄
3里所講得僅僅是抽取一條記錄的情況，當(dāng)我們需要抽取多條記錄的時(shí)候，此時(shí)在2中的N的取值應(yīng)該是在N >= (M + X - 1)這個(gè)范圍內(nèi)，當(dāng)讓最經(jīng)濟(jì)的取值就是取等好的時(shí)候了的時(shí)候了。當(dāng)然最后的抽取條件也不是RECNO = N了，應(yīng)該是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)
同樣以上面的數(shù)據(jù)為例，則抽取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)
結(jié)果如下：
ID NAME
05 fifth
01 first
04 forth
以此為基礎(chǔ)，再擴(kuò)展的話，做成存儲(chǔ)過(guò)程，將開(kāi)始記錄數(shù)以及抽取記錄數(shù)為參數(shù)，就可以輕松實(shí)現(xiàn)分頁(yè)抽取數(shù)據(jù)。
當(dāng)然了，上面所講的都是一些最基本的，實(shí)際應(yīng)用中往往都沒(méi)有這么簡(jiǎn)單，但是不管怎么說(shuō)，不管復(fù)雜的應(yīng)用總是由這些簡(jiǎn)單的元素構(gòu)成，掌握一些最基本的方法始終是重要的。