一個員工信息表
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
結果如下: