?? 解析oracle的rownum - 數據庫 - whatiswhat.htm
字號:
<TABLE style="BORDER-COLLAPSE: collapse; WORD-WRAP: break-word"
cellSpacing=0 cellPadding=0 width="100%" border=0>
<TBODY>
<TR>
<TD align=middle>
<TABLE
style="BORDER-COLLAPSE: collapse; WORD-WRAP: break-word"
cellSpacing=0 cellPadding=0 width="100%" border=0>
<TBODY>
<TR>
<TD>
<DIV id=art style="MARGIN: 15px">
<P
style="COLOR: rgb(0,1,2)">對于rownum來說它是oracle系統順序分配為從查詢返回的行的編號，返回的第一行分配的是1，第二行是2，依此類推，這個偽字段可以用于限制查詢返回的總行數，而且rownum不能以任何表的名稱作為前綴。<BR> 舉例說明：<BR>例如表：student(學生)表，表結構為：<BR>ID
char(6) --學號<BR>name VARCHAR2(10) --姓名
<BR>create table student (ID char(6), name
VARCHAR2(100));<BR>insert into sale
values('200001',‘張一’);<BR>insert into sale
values('200002',‘王二’);<BR>insert into sale
values('200003',‘李三’);<BR>insert into sale
values('200004',‘趙四’);<BR>commit;<BR><BR>(1) rownum
對于等于某值的查詢條件<BR>如果希望找到學生表中第一條學生的信息，可以使用rownum=1作為條件。但是想找到學生表中第二條學生的信息，使用rownum=2結果查不到數據。因為rownum都是從1開始，但是1以上的自然數在rownum做等于判斷是時認為都是false條件，所以無法查到rownum
= n（n>1的自然數）。<BR>SQL> select rownum,id,name from
student where
rownum=1;（可以用在限制返回記錄條數的地方，保證不出錯，如：隱式游標）<BR>SQL>
select rownum,id,name from student where
rownum=1;<BR> ROWNUM
ID NAME<BR>---------- ------
---------------------------------------------------<BR>
1 200001 張一<BR>SQL> select rownum,id,name from
student where rownum =2; <BR> ROWNUM
ID NAME<BR>---------- ------
---------------------------------------------------</P>
<P
style="COLOR: rgb(0,1,2)">（2）rownum對于大于某值的查詢條件<BR>
如果想找到從第二行記錄以后的記錄，當使用rownum>2是查不出記錄的，原因是由于rownum是一個總是從1開始的偽列，Oracle
認為rownum> n(n>1的自然數)這種條件依舊不成立，所以查不到記錄<BR>SQL>
select rownum,id,name from student where rownum
>2;<BR>ROWNUM ID
NAME<BR>---------- ------
---------------------------------------------------<BR>那如何才能找到第二行以后的記錄呀。可以使用以下的子查詢方法來解決。注意子查詢中的rownum必須要有別名，否則還是不會查出記錄來，這是因為rownum不是某個表的列，如果不起別名的話，無法知道rownum是子查詢的列還是主查詢的列。<BR>SQL>select
* from(select rownum no ,id,name from student) where
no>2;<BR>
NO ID NAME<BR>---------- ------
---------------------------------------------------<BR>
3 200003
李三<BR> 4
200004 趙四<BR>SQL> select * from(select rownum,id,name
from student)where rownum>2;<BR>
ROWNUM ID NAME<BR>----------
------
---------------------------------------------------</P>
<P
style="COLOR: rgb(0,1,2)">（3）rownum對于小于某值的查詢條件<BR>如果想找到第三條記錄以前的記錄，當使用rownum<3是能得到兩條記錄的。顯然rownum對于rownum<n（(n>1的自然數）的條件認為是成立的，所以可以找到記錄。<BR>SQL>
select rownum,id,name from student where rownum
<3;<BR> ROWNUM
ID NAME<BR>---------- ------
---------------------------------------------------<BR>
1 200001
張一<BR> 2
200002
王二<BR>綜上幾種情況，可能有時候需要查詢rownum在某區間的數據，那怎么辦呀從上可以看出rownum對小于某值的查詢條件是人為true的，rownum對于大于某值的查詢條件直接認為是false的，但是可以間接的讓它轉為認為是true的。那就必須使用子查詢。例如要查詢rownum在第二行到第三行之間的數據，包括第二行和第三行數據，那么我們只能寫以下語句，先讓它返回小于等于三的記錄行，然后在主查詢中判斷新的rownum的別名列大于等于二的記錄行。但是這樣的操作會在大數據集中影響速度。<BR>SQL>
select * from (select rownum no,id,name from student
where rownum<=3 ) where no
>=2;<BR> NO
ID NAME<BR>---------- ------
---------------------------------------------------<BR>
2 200002
王二<BR> 3
200003 李三</P>
<P
style="COLOR: rgb(0,1,2)">（4）rownum和排序<BR>Oracle中的rownum的是在取數據的時候產生的序號，所以想對指定排序的數據去指定的rowmun行數據就必須注意了。<BR>SQL>
select rownum ,id,name from student order by
name;<BR> ROWNUM
ID NAME<BR>---------- ------
---------------------------------------------------<BR>
3 200003
李三<BR> 2
200002
王二<BR> 1
200001
張一<BR> 4
200004
趙四<BR>可以看出，rownum并不是按照name列來生成的序號。系統是按照記錄插入時的順序給記錄排的號，rowid也是順序分配的。為了解決這個問題，必須使用子查詢<BR>SQL>
select rownum ,id,name from (select * from student order
by name);<BR> ROWNUM
ID NAME<BR>---------- ------
---------------------------------------------------<BR>
1 200003
李三<BR> 2
200002
王二<BR> 3
200001
張一<BR> 4
200004
趙四<BR>這樣就成了按name排序，并且用rownum標出正確序號（有小到大）<BR>筆者在工作中有一上百萬條記錄的表，在jsp頁面中需對該表進行分頁顯示，
便考慮用rownum來作，下面是具體方法(每頁<BR>顯示20條)： <BR> “select *
from tabname where rownum<20 order by name"
但卻發現oracle卻不能按自己的意愿來執行，而是先隨便<BR>取20條記錄，然后再 order
by，后經咨詢oracle,說rownum確實就這樣，想用的話，只能用子查詢
來實現先排序，后<BR>rownum，方法如下： <BR> "select * from
(select * from tabname order by name) where
rownum<20",但這樣一來，效率會較低很多。 <BR> 后經筆者試驗，只需在order
by 的字段上加主鍵或索引即可讓oracle先按 該字段排序，然后再rownum；方法不變：
<BR> “select * from tabname where
rownum<20 order by name"</P>
<P style="COLOR: rgb(0,1,2)">取得某列中第N大的行 </P>
<P style="COLOR: rgb(0,1,2)">select column_name from
<BR>(select table_name.*,dense_rank() over (order by
column desc) rank from table_name) <BR>where rank =
&N； <BR> 假如要返回前5條記錄： </P>
<P style="COLOR: rgb(0,1,2)"> select * from tablename
where rownum<6;(或是rownum <= 5 或是rownum != 6)
<BR>假如要返回第5-9條記錄： </P>
<P style="COLOR: rgb(0,1,2)">select * from tablename
<BR>where … <BR>and rownum<10 <BR>minus <BR>select *
from tablename <BR>where … <BR>and rownum<5 <BR>order
by name <BR>選出結果后用name排序顯示結果。(先選再排序) </P>
<P style="COLOR: rgb(0,1,2)">注意：只能用以上符號(<、<=、!=)。
</P>
<P style="COLOR: rgb(0,1,2)">select * from tablename
where rownum != 10;返回的是前９條記錄。
<BR>不能用：>,>=,=,Between...and。由于rownum是一個總是從1開始的偽列，Oracle
認為這種條件 不成立，查不到記錄. </P>
<P style="COLOR: rgb(0,1,2)">另外，這個方法更快： </P>
<P style="COLOR: rgb(0,1,2)">select * from ( <BR>select
rownum r,a from yourtable <BR>where rownum <= 20
<BR>order by name ) <BR>where r > 10
<BR>這樣取出第11-20條記錄!(先選再排序再選) </P>
<P style="COLOR: rgb(0,1,2)">要先排序再選則須用select嵌套：內層排序外層選。
<BR>rownum是隨著結果集生成的，一旦生成，就不會變化了；同時,生成的結果是依次遞加的，沒有1就永遠不會有2!
<BR>rownum 是在 查詢集合產生的過程中產生的偽列，并且如果where條件中存在 rownum
條件的話，則: </P>
<P style="COLOR: rgb(0,1,2)">1： 假如 判定條件是常量，則： <BR>只能
rownum = 1, <= 大于1 的自然數， = 大于1 的數是沒有結果的， 大于一個數也是沒有結果的
<BR>即 當出現一個 rownum 不滿足條件的時候則 查詢結束 this is stop key!
</P><SPAN style="COLOR: rgb(0,1,2)">2: 當判定值不是常量的時候
</SPAN><BR style="COLOR: rgb(0,1,2)"><SPAN
style="COLOR: rgb(0,1,2)">若條件是 = var , 則只有當 var 為1
的時候才滿足條件，這個時候不存在 stop key ,必須進行 full scan
,對每個滿足其他where條件的數據進行判定 </SPAN><BR
style="COLOR: rgb(0,1,2)"><SPAN
style="COLOR: rgb(0,1,2)">選出一行后才能去選rownum=2的行……
</SPAN></DIV></TD></TR></TBODY></TABLE>
<P style="MARGIN: 5px; LINE-HEIGHT: 150%"></P></TD></TR>
<TR>
<TD align=middle height=25><FONT color=#295200>發表于： 2008-06-17
，修改于： 2008-06-17 12:13，已瀏覽14次，有評論0條</FONT> <A id=star
title=推薦這篇文章 onclick="NewWindows(this.href);return false;"
href="http://blog.chinaunix.net/u2/star.php?blogid=44734&artid=1003605">推薦</A>
<A id=complaint title=投訴這篇文章
onclick="NewWindows(this.href);return false;"
href="http://blog.chinaunix.net/u2/complaint.php?blogid=44734&artid=1003605">投訴</A>
</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE></TD>
<TD width=18
background="解析oracle的ROWNUM - 數據庫 - whatiswhat.files/bg_art_right.gif"></TD></TR>
<TR>
<TD width=18 height=28><IMG alt=""
src="解析oracle的ROWNUM - 數據庫 - whatiswhat.files/bg_art_left_bottom.gif"
border=0></TD>
<TD
background="解析oracle的ROWNUM - 數據庫 - whatiswhat.files/bg_art_bottom.gif"><P
style="MARGIN: 5px; LINE-HEIGHT: 150%"></P></TD>
<TD width=18 height=28><IMG alt=""
src="解析oracle的ROWNUM - 數據庫 - whatiswhat.files/bg_art_right_bottom.gif"
border=0></TD></TR></TBODY></TABLE><BR>
<TABLE style="BORDER-COLLAPSE: collapse" borderColor=#a5bd6b cellSpacing=1
cellPadding=0 width="90%" align=center border=1>
<TBODY>
<TR>
<TD style="COLOR: #295200" bgColor=#eff7de height=25><B>網友評論</B></TD></TR>
<TR>
<TD bgColor=#ffffff height=1></TD></TR>
<TR>
<TD align=middle bgColor=#f9f5e7>
<TABLE
style="COLOR: #295200; BORDER-COLLAPSE: collapse; WORD-WRAP: break-word"
cellSpacing=0 cellPadding=0 width="100%" align=center border=0>
<TBODY></TBODY></TABLE></TD></TR></TBODY></TABLE><BR>
<TABLE style="BORDER-COLLAPSE: collapse" borderColor=#a5bd6b cellSpacing=1
cellPadding=0 width="90%" align=center border=1>
<TBODY>
<TR>
<TD style="COLOR: #295200" bgColor=#eff7de height=25><B>發表評論</B></TD></TR>
<TR>
<TD bgColor=#ffffff height=1></TD></TR>
<TR>
<TD align=middle bgColor=#f9f5e7><IFRAME name=comment
src="解析oracle的ROWNUM - 數據庫 - whatiswhat.files/comment.htm" frameBorder=0
width="100%" height=160></IFRAME></TD></TR></TBODY></TABLE></BODY></HTML>
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -