Oracle 中 row_number()、rank()、dense_rank() 函數(shù)的用法詳解
一、row_number() 函數(shù)
在前面使用rownum實(shí)現(xiàn)分頁,雖然是可以實(shí)現(xiàn)的,但是看似是否有點(diǎn)別扭。因為當(dāng)需要對分頁排序時,rownum總是先生成序列號再排序,其實(shí)這不時我們想要的。而row_number()函數(shù)則是先排序,再生成序列號。這也是row_number與rownum主要的區(qū)別。下面來看row_number()的使用:
語法:row_number() over([partition by col1] order by col2 [ASC | DESC] [,col3 [ASC | DESC]]...)
參數(shù)解釋:
row_number() over():是固定寫法,即不能單獨(dú)使用row_nubmer()函數(shù);
partition by:可選的。用于指定分組(或分開依據(jù))的列,類似SELECT中的group by子句;
order by:用于指定排序的列,類似SELECT中的order by子句。
1.基本用法
SELECTrow_number()over(orderbyempno)ASrnum,t1.*FROMemp t1;
2.使用row_number()分頁
SELECT * FROM ( SELECT row_number() over(order by empno) AS rnum, t1.* FROM emp t1 ) t WHERE t.rnum BETWEEN 4 AND 6;
3.使用partition by參數(shù)分區(qū)生成序號
當(dāng)使用partition by參數(shù)時,序號將可能不是唯一的,因為序號的生成只會在當(dāng)前分區(qū)中唯一,下一個分區(qū)又將從1開始計算,例如:
SELECTrow_number()over(partitionbydeptnoorderbyempno)ASrnum,t1.*FROMemp t1;
二、 rank()與dense_rank()函數(shù)
rank()與row_number()的區(qū)別在于,rank()會按照排序值相同的為一個序號(以下稱為:塊),第二個不同排序值將顯示所有行的遞增值,而不是當(dāng)前序號加1??词纠?/p>
SELECTrank()over(orderbyjob)rnum,job,enameFROMemp t1;
而dense_rank()函數(shù),與rank()區(qū)別在于,第二個不同排序值,是對當(dāng)前序號值加1,看示例:
SELECTdense_rank()over(orderbyjob)rnum,job,enameFROMemp t1;
當(dāng)指定partition by參數(shù)時,將根據(jù)指定的字段分組,進(jìn)行分組計算序號值,序號值只在當(dāng)前分組中有效,例如:
SELECTrank()over(partitionbydeptnoorderbyjob)rnum,job,ename,deptnoFROMemp t1;
SELECTdense_rank()over(partitionbydeptnoorderbyjob)rnum,job,ename,deptnoFROMemp t1;
三、 over()函數(shù)結(jié)合聚合函數(shù)的使用
SELECTempno,ename,sal,hiredate,COUNT(sal)OVER(ORDERBYhiredateDESC)countFROMemp;
SELECTempno,ename,sal,hiredate,MAX(sal)OVER(ORDERBYhiredateASC)maxFROMemp;
SELECTempno,ename,sal,hiredate,MIN(sal)OVER(ORDERBYhiredateDESC)minFROMemp;
SELECTempno,ename,sal,hiredate,AVG(sal)OVER(ORDERBYhiredateDESC)avgFROMemp;
SELECTempno,ename,sal,hiredate,SUM(sal)OVER(ORDERBYhiredateDESC)sumFROMemp;
四、 綜合案例
1)查詢前100條記錄
SELECT*FROMempWHERErownum<=100;
注意:如果以上語句需要排序后再篩選,并不是能使用rownum實(shí)現(xiàn),而需要使用row_number()函數(shù)。
2)查出4 ~ 6條的記錄,并按員工編號排序(分頁運(yùn)用)
SELECT*FROM(SELECTrow_number()over(orderbyempno)rnum,t.*FROMemp t)t WHEREt.rnum>=4ANDt.rnum<=6;
3)查出每個部門工資最高的員工
SELECT*FROM(SELECTrow_number()over(partitionbydeptnoorderbysalDESC)rnum,t.*FROMemp t)tWHEREt.rnum=1;
4)查出每個部門工資最高的所有員工(排名并列的)
SELECT*FROM(SELECTrank()over(partitionbydeptnoorderbysalDESC)rnum,t.*FROMemp t)tWHEREt.rnum=1;
5)查出每個部門工資排名第三的所有員工(排名并列的)
SELECT*FROM(SELECTdense_rank()over(partitionbydeptnoorderbysalASC)rnum,t.*FROMemp t)tWHEREt.rnum=3;
注意:如果使用rank()是不行的,因為20號部門并列第二的員工有2個,序號3就被跳掉了,直接跳到了序號4,使用以下語句可以查看到:
SELECTrank()over(partitionbydeptnoorderbysalASC)rnum,t.*FROMemp t;
所以,使用rank()將會得到錯誤的結(jié)果:
SELECTrank()over(partitionbydeptnoorderbysalASC)rnum,t.*FROMemp t;
五、 總結(jié)
1.如果需要取前多少條記錄,就使用rownum偽列。rownum就類似于SQL Server TOP子句的用法,但是rownum不能用于排序并過濾的場合
2.如果取多少條到多少條的記錄(分頁),就是使用row_number()函數(shù)。
例如:查出4 ~ 6條的記錄,并按員工編號排序
3.如果取某個組別中最大值記錄或最小值的記錄,也可以使用row_number()函數(shù),并結(jié)合partition by參數(shù)。
例如:查出每個部門工資最高的員工。
4.如果取某個組別中并列最大值或最小值得記錄,就使用rank()函數(shù),并結(jié)合partition by參數(shù)。
例如:查出每個部門工資最高的所有員工。
5.如果取某個組別中并列排名幾記錄,就使用dense_rank()函數(shù),并結(jié)合partition by參數(shù)。
例如:查出每個部門工資排名第三的所有員工。
到此這篇關(guān)于Oracle 中 row_number()、rank()、dense_rank() 函數(shù)的用法的文章就介紹到這了,更多相關(guān)Oracle row_number()、rank()、dense_rank() 函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
oracle生成動態(tài)前綴且自增號碼的函數(shù)分享
這篇文章主要介紹了oracle生成動態(tài)前綴且自增號碼的函數(shù),需要的朋友可以參考下2014-04-04解決Oracle刪除重復(fù)數(shù)據(jù)只留一條的方法詳解
本篇文章是對Oracle刪除重復(fù)數(shù)據(jù)只留一條的解決方法進(jìn)行了詳細(xì)的分析介紹,需要的朋友參考下2013-05-05