引用

trailblizer 的 Oracle:Rank,Dense_Rank,Row_Number比較

Oracle:Rank,Dense_Rank,Row_Number比較

一個(gè)員工信息表

Create Table EmployeeInfo (CODE Number(3) Not Null,EmployeeName varchar2(15),DepartmentID Number(3),Salary NUMBER(7,2),

Constraint PK_EmployeeInfo Primary Key (CODE));

Select * From EMPLOYEEINFO

現(xiàn)執(zhí)行SQL語(yǔ)句：

Select EMPLOYEENAME,SALARY,

RANK() OVER (Order By SALARY Desc)  "RANK",

DENSE_RANK() OVER (Order By SALARY Desc ) "DENSE_RANK",

ROW_NUMBER() OVER(Order By SALARY Desc) "ROW_NUMBER"

 From EMPLOYEEINFO

結(jié)果如下：

Rank，Dense_rank，Row_number函數(shù)為每條記錄產(chǎn)生一個(gè)從1開(kāi)始至N的自然數(shù)，N的值可能小于等于記錄的總數(shù)。這3個(gè)函數(shù)的唯一區(qū)別在于當(dāng)碰到相同數(shù)據(jù)時(shí)的排名策略。

①ROW_NUMBER：

Row_number函數(shù)返回一個(gè)唯一的值，當(dāng)碰到相同數(shù)據(jù)時(shí)，排名按照記錄集中記錄的順序依次遞增。

②DENSE_RANK：

Dense_rank函數(shù)返回一個(gè)唯一的值，除非當(dāng)碰到相同數(shù)據(jù)時(shí)，此時(shí)所有相同數(shù)據(jù)的排名都是一樣的。

③RANK：

Rank函數(shù)返回一個(gè)唯一的值，除非遇到相同的數(shù)據(jù)時(shí)，此時(shí)所有相同數(shù)據(jù)的排名是一樣的，同時(shí)會(huì)在最后一條相同記錄和下一條不同記錄的排名之間空出排名。

同時(shí)也可以分組排序,也就是在Over從句內(nèi)加入Partition by groupField：

 Select DEPARTMENTID,EMPLOYEENAME,SALARY,

RANK() OVER ( Partition By DEPARTMENTID Order By SALARY Desc)  "RANK",

DENSE_RANK() OVER ( Partition By DEPARTMENTID Order By SALARY Desc ) "DENSE_RANK",

ROW_NUMBER() OVER( Partition By DEPARTMENTID Order By SALARY Desc) "ROW_NUMBER"

 From EMPLOYEEINFO

結(jié)果如下：

現(xiàn)在如果插入一條工資為空的記錄，那么執(zhí)行上述語(yǔ)句，結(jié)果如下：

會(huì)發(fā)現(xiàn)空值的竟然排在了第一位，這顯然不是想要的結(jié)果。解決的辦法是在Over從句Order By后加上 NULLS Last即：

Select EMPLOYEENAME,SALARY,

RANK() OVER (Order By SALARY Desc  Nulls Last)  "RANK",

DENSE_RANK() OVER (Order By SALARY Desc Nulls Last) "DENSE_RANK",

ROW_NUMBER() OVER(Order By SALARY Desc Nulls Last ) "ROW_NUMBER"

 From EMPLOYEEINFO

結(jié)果如下：