ROW_NUMBER()
Row_number函数返回一个唯一的值,当碰到相同数据时,排名按照记录集中记录的顺序依次递增。
说明:返回结果集分区内行的序列号,每个分区的第一行从 1 开始。
语法:ROW_NUMBER () OVER ( [ <partition_by_clause> ] <order_by_clause> ) 。
备注:ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。
参数:<partition_by_clause> :将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。
<order_by_clause>:确定将 ROW_NUMBER 值分配给分区中的行的顺序。
RANK()
Rank函数返回一个唯一的值,除非遇到相同的数据时,此时所有相同数据的排名是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
说明:返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。
语法:RANK () OVER ( [ < partition_by_clause > ] < order_by_clause > )
备注:如果两个或多个行与一个排名关联,则每个关联行将得到相同的排名。 例如,如果两位顶尖销售员具有同样的 SalesYTD 值,他们将并列第一。由于已有两行排名在前,所以具有下一个最大 SalesYTD 的销售人员将排名第三。 因此,RANK 函数并不总返回连续整数。用于整个查询的排序顺序决定了行在结果集中的显示顺序。这也隐含了行在每个分区中的排名。
参数:< partition_by_clause > :将 FROM 子句生成的结果集划分为要应用 RANK 函数的分区。
< order_by_clause >:确定将 RANK 值应用于分区中的行时所基于的顺序。
DENSE_RANK()
Dense_rank函数返回一个唯一的值,除非当碰到相同数据时,此时所有相同数据的排名都是一样的。
说明:返回结果集分区中行的排名,在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一。
语法:DENSE_RANK () OVER ( [ < partition_by_clause > ] < order_by_clause > )
备注:如果有两个或多个行受同一个分区中排名的约束,则每个约束行将接收相同的排名。例如,如果两位顶尖销售员具有相同的SalesYTD 值,则他们将并列第一。接下来 SalesYTD 最高的销售人员排名第二。该排名等于该行之前的所有行数加一。 因此,DENSE_RANK 函数返回的数字没有间断,并且始终具有连续的排名。整个查询所用的排序顺序确定了各行在结果中的显示顺序。这说明排名第一的行可以不是分区中的第一行。
参数:< partition_by_clause > :将 FROM 子句所生成的结果集划分为数个将应用 DENSE_RANK 函数的分区。
< order_by_clause >:确定将 DENSE_RANK 值应用于分区中各行的顺序。
示例:以Oracle中的emp表的数据为例
select empno,
deptno,
sal,
rank() over(partition by deptno order by sal desc nulls last) as rank,
dense_rank() over(partition by deptno order by sal desc nulls last) as dense_rank,
row_number() over(partition by deptno order by sal desc nulls last) as row_number
from emp