一、row_number() 函數(shù)

在前面使用rownum實(shí)現(xiàn)分頁(yè)，雖然是可以實(shí)現(xiàn)的，但是看似是否有點(diǎn)別扭。因?yàn)楫?dāng)需要對(duì)分頁(yè)排序時(shí)，rownum總是先生成序列號(hào)再排序，其實(shí)這不時(shí)我們想要的。而row_number()函數(shù)則是先排序，再生成序列號(hào)。這也是row_number與rownum主要的區(qū)別。下面來(lái)看row_number()的使用:

語(yǔ)法：row_number() over([partition by col1] order by col2 [ASC | DESC] [,col3 [ASC | DESC]]...)

參數(shù)解釋：

row_number() over():是固定寫(xiě)法，即不能單獨(dú)使用row_nubmer()函數(shù)；

partition by:可選的。用于指定分組（或分開(kāi)依據(jù)）的列，類似SELECT中的group by子句；

order by:用于指定排序的列，類似SELECT中的order by子句。

1.基本用法

SELECTrow_number()over(orderbyempno)ASrnum,t1.*FROMemp t1;

2.使用row_number()分頁(yè)

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ū)生成序號(hào)

當(dāng)使用partition by參數(shù)時(shí)，序號(hào)將可能不是唯一的，因?yàn)樾蛱?hào)的生成只會(huì)在當(dāng)前分區(qū)中唯一，下一個(gè)分區(qū)又將從1開(kāi)始計(jì)算，例如：

SELECTrow_number()over(partitionbydeptnoorderbyempno)ASrnum,t1.*FROMemp t1;

二、 rank()與dense_rank()函數(shù)

rank()與row_number()的區(qū)別在于，rank()會(huì)按照排序值相同的為一個(gè)序號(hào)（以下稱為：塊），第二個(gè)不同排序值將顯示所有行的遞增值，而不是當(dāng)前序號(hào)加1?？词纠?/p>

SELECTrank()over(orderbyjob)rnum,job,enameFROMemp t1;

而dense_rank()函數(shù)，與rank()區(qū)別在于，第二個(gè)不同排序值，是對(duì)當(dāng)前序號(hào)值加1，看示例：

SELECTdense_rank()over(orderbyjob)rnum,job,enameFROMemp t1;

當(dāng)指定partition by參數(shù)時(shí)，將根據(jù)指定的字段分組，進(jìn)行分組計(jì)算序號(hào)值，序號(hào)值只在當(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;

注意：如果以上語(yǔ)句需要排序后再篩選，并不是能使用rownum實(shí)現(xiàn)，而需要使用row_number()函數(shù)。

2)查出4 ~ 6條的記錄，并按員工編號(hào)排序（分頁(yè)運(yùn)用）

SELECT*FROM(SELECTrow_number()over(orderbyempno)rnum,t.*FROMemp t)t
WHEREt.rnum>=4ANDt.rnum<=6;

3)查出每個(gè)部門(mén)工資最高的員工

SELECT*FROM(SELECTrow_number()over(partitionbydeptnoorderbysalDESC)rnum,t.*FROMemp t)tWHEREt.rnum=1;

4)查出每個(gè)部門(mén)工資最高的所有員工（排名并列的）

SELECT*FROM(SELECTrank()over(partitionbydeptnoorderbysalDESC)rnum,t.*FROMemp t)tWHEREt.rnum=1;

5)查出每個(gè)部門(mén)工資排名第三的所有員工（排名并列的）

SELECT*FROM(SELECTdense_rank()over(partitionbydeptnoorderbysalASC)rnum,t.*FROMemp t)tWHEREt.rnum=3;

注意：如果使用rank()是不行的，因?yàn)?0號(hào)部門(mén)并列第二的員工有2個(gè)，序號(hào)3就被跳掉了，直接跳到了序號(hào)4，使用以下語(yǔ)句可以查看到：

SELECTrank()over(partitionbydeptnoorderbysalASC)rnum,t.*FROMemp t;

所以，使用rank()將會(huì)得到錯(cuò)誤的結(jié)果：

SELECTrank()over(partitionbydeptnoorderbysalASC)rnum,t.*FROMemp t;

五、 總結(jié)

1.如果需要取前多少條記錄，就使用rownum偽列。rownum就類似于SQL Server TOP子句的用法，但是rownum不能用于排序并過(guò)濾的場(chǎng)合

2.如果取多少條到多少條的記錄（分頁(yè)），就是使用row_number()函數(shù)。

例如：查出4 ~ 6條的記錄，并按員工編號(hào)排序

3.如果取某個(gè)組別中最大值記錄或最小值的記錄，也可以使用row_number()函數(shù)，并結(jié)合partition by參數(shù)。

例如：查出每個(gè)部門(mén)工資最高的員工。

4.如果取某個(gè)組別中并列最大值或最小值得記錄，就使用rank()函數(shù)，并結(jié)合partition by參數(shù)。

例如：查出每個(gè)部門(mén)工資最高的所有員工。

5.如果取某個(gè)組別中并列排名幾記錄，就使用dense_rank()函數(shù)，并結(jié)合partition by參數(shù)。

例如：查出每個(gè)部門(mén)工資排名第三的所有員工。

到此這篇關(guān)于Oracle 中 row_number()、rank()、dense_rank() 函數(shù)的用法的文章就介紹到這了,更多相關(guān)Oracle row_number()、rank()、dense_rank() 函數(shù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論