Oracle分級排序函數Rank、Dense_Rank、Row_Number比較

轉自：http://diwuningning.blog.163.com/blog/static/3426519520102252934792/

 

一個員工資訊表

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

現執行SQL語句：

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

結果如下：

Rank，Dense_rank，Row_number函數為每條記錄產生一個從1開始至N的自然數，N的值可能小於等於記錄的總數。這3個函數的唯一區別在於當碰到相同資料時的排名策略。

①ROW_NUMBER：

Row_number函數返回一個唯一的值，當碰到相同資料時，排名按照記錄集中記錄的順序依次遞增。

②DENSE_RANK：

Dense_rank函數返回一個唯一的值，除非當碰到相同資料時，此時所有相同資料的排名都是一樣的。

③RANK：

Rank函數返回一個唯一的值，除非遇到相同的資料時，此時所有相同資料的排名是一樣的，同時會在最後一條相同記錄和下一條不同記錄的排名之間空出排名。

同時也可以分組排序,也就是在Over從句內加入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

結果如下：

現在如果插入一條工資為空白的記錄，那麼執行上述語句，結果如下：

會發現空值的竟然排在了第一位，這顯然不是想要的結果。解決的辦法是在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

結果如下：