row_number()over(partition by col1 order by col2)表示根據(jù)col1分組，在分組內(nèi)部根據(jù)col2排序，而此函數(shù)計(jì)算的值就表示每組內(nèi)部排序后的順序編號（組內(nèi)連續(xù)的唯一的）。 與rownum的區(qū)別在于：使用rownum進(jìn)行排序的時(shí)候是先對結(jié)果集加入偽劣rownum然后再進(jìn)行排序，而row_number()在包含排序從句后是先排序再計(jì)算行號碼。

一、oracle中rownum

用于從查詢返回的行的編號，返回的第一行分配的是1，第二行是2，依此類推，這個(gè)偽字段可以用于限制查詢返回的總行數(shù)，而且rownum不能以任何表的名稱作為前綴。

1、rownum 對于等于某值的查詢條件

如果希望找到學(xué)生表中第一條學(xué)生的信息，可以使用rownum=1作為條件。但是想找到學(xué)生表中第二條學(xué)生的信息，使用rownum=2結(jié)果查不到數(shù)據(jù)。因?yàn)閞ownum都是從1開始，但是1以上的自然數(shù)在rownum做等于判斷是時(shí)認(rèn)為都是false條件，所以無法查到rownum = n（n>1的自然數(shù)）。
SQL> select rownum,id,name from student where rownum=1;   --有記錄
SQL> select rownum,id,name from student where rownum =2;  --無記錄

2、rownum對于大于某值的查詢條件

如果想找到從第二行記錄以后的記錄，當(dāng)使用rownum>2是查不出記錄的，可以使用以下的子查詢方法來解決。注意子查詢中的rownum必須要有別名，否則還是不會(huì)查出記錄來，這是因?yàn)閞ownum不是某個(gè)表的列，如果不起別名的話，無法知道rownum是子查詢的列還是主查詢的列。
SQL>select * from(select rownum no ,id,name from student) where no>2;  --有記錄
SQL> select * from(select rownum,id,name from student)where rownum>2; --無記錄

3、rownum對于小于某值的查詢條件

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

4、rownum和排序

Oracle中的rownum的是在取數(shù)據(jù)的時(shí)候產(chǎn)生的序號，所以想對指定排序的數(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列來生成的序號。系統(tǒng)是按照記錄插入時(shí)的順序給記錄排的號，rowid也是順序分配的。為了解決這個(gè)問題，必須使用子查詢

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

    ROWNUM ID     NAME
---------- ------ ---------------------------------------------------
         1 200003 李三
         2 200002 王二
         3 200001 張一
         4 200004 趙四

二、oracle中row_number()

1、row_number() over (order by col_1[,col_2 ...])
按照col_1[,col_2 ...]排序,返回排序后的結(jié)果集，并且為每一行返回一個(gè)不相同的值。

2、row_number() over (partition by col_n[,col_m ...] order by col_1[,col_2 ...])
先按照col_n[,col_m ...進(jìn)行分組，再在每個(gè)分組中按照col_1[,col_2 ...]進(jìn)行排序(升序)，最后返回排好序后的結(jié)果集

oracle中row_number()實(shí)例

1.使用row_number（）函數(shù)進(jìn)行編號，如
select email,customerID, ROW_NUMBER() over(order by psd) as rows from QT_Customer
原理：先按psd進(jìn)行排序，排序完后，給每條數(shù)據(jù)進(jìn)行編號。

2.在訂單中按價(jià)格的升序進(jìn)行排序，并給每條記錄進(jìn)行排序代碼如下：
select DID,customerID,totalPrice,ROW_NUMBER() over(order by totalPrice) as rows from OP_Order

3、統(tǒng)計(jì)每一個(gè)客戶最近下的訂單是第幾次下的訂單。
with tabs as 
( 
select ROW_NUMBER() over(partition by customerID  order by totalPrice) as rows,customerID,totalPrice, DID from OP_Order 
) 
select MAX(rows) as '下單次數(shù)',customerID from tabs group by customerID

4、在使用over等開窗函數(shù)時(shí)，over里頭的分組及排序的執(zhí)行晚于“where，group by，order by”的執(zhí)行。
select  
ROW_NUMBER() over(partition by customerID  order by insDT) as rows, 
customerID,totalPrice, DID 
from OP_Order where insDT>'2011-07-22'
以上代碼是先執(zhí)行where子句，執(zhí)行完后，再給每一條記錄進(jìn)行編號。

三、row_number()與rownum的區(qū)別

使用rownum進(jìn)行排序的時(shí)候是先對結(jié)果集加入偽劣rownum然后再進(jìn)行排序，而row_number()在包含排序從句后是先排序再計(jì)算行號碼。

以上所述就是本文的全部內(nèi)容了，希望大家能夠喜歡。

最新評論