rank,dense_rank,row_number区别
一：语法(用法)：
rank() over([partition by col1] order by col2)
dense_rank() over([partition by col1] order by col2)
row_number() over([partition by col1] order by col2)
其中[partition by col1]可省略。
二：区别
三个分析函数都是按照col1分组内从1开始排序
row_number() 是没有重复值的排序(即使两天记录相等也是不重复的)，可以利用它来实现分页
dense_rank() 是连续排序，两个第二名仍然跟着第三名
rank() 是跳跃排序，两个第二名下来就是第四名
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 值分配给分区中的行的顺序。
返回类型：bigint 。
rank()
说明：返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。
语法：RANK () OVER ( [ < partition_by_clause > ] < order_by_clause > )
备注：如果两个或多个行与一个排名关联，则每个关联行将得到相同的排名。
例如，如果两位顶尖销售员具有同样的 SalesYTD 值，他们将并列第一。
由于已有两行排名在前，所以具有下一个最大 SalesYTD 的销售人员将排名第三。
因此，RANK 函数并不总返回连续整数。
用于整个查询的排序顺序决定了行在结果集中的显示顺序。这也隐含了行在每个分区中的排名。
参数：< partition_by_clause > ：将 FROM 子句生成的结果集划分为要应用 RANK 函数的分区。
< order_by_clause >：确定将 RANK 值应用于分区中的行时所基于的顺序。
返回类型：bigint
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 值应用于分区中各行的顺序。
返回类型：bigint
下面转载某位大哥写的，原地址：http://blog.csdn.net/winer2008/article/details/4283539
SQL> select * from t; NAME SCORE ---------- ----- 语文 60 语文 90 语文 80 语文 80 数学 67 数学 77 数学 78 数学 88 数学 99 语文 70 10 rows selected SQL> select name,score,rank() over(partition by name order by score) tt from t; NAME SCORE TT ---------- ----- ---------- 数学 67 1 数学 77 2 数学 78 3 数学 88 4 数学 99 5 语文 60 1 语文 70 2 语文 80 3 <---- 语文 80 3 <---- 语文 90 5 10 rows selected SQL> select name,score,dense_rank() over(partition by name order by score) tt from t; NAME SCORE TT ---------- ----- ---------- 数学 67 1 数学 77 2 数学 78 3 数学 88 4 数学 99 5 语文 60 1 语文 70 2 语文 80 3 <---- 语文 80 3 <---- 语文 90 4 10 rows selected SQL> select name,score,row_number() over(partition by name order by score) tt from t; NAME SCORE TT ---------- ----- ---------- 数学 67 1 数学 77 2 数学 78 3 数学 88 4 数学 99 5 语文 60 1 语文 70 2 语文 80 3 <---- 语文 80 4 <---- 语文 90 5 10 rows selected SQL> select name,score,rank() over(order by score) tt from t; NAME SCORE TT ---------- ----- ---------- 语文 60 1 数学 67 2 语文 70 3 数学 77 4 数学 78 5 语文 80 6 语文 80 6 数学 88 8 语文 90 9 数学 99 10 10 rows selected
应用
一：dense_rank------------------查询每门功课前三名
select name,score from (select name,score,dense_rank() over(partition by name order by score desc) tt from t) x where x.tt<=3
NAME SCORE
---------- -----
数学 99
数学 88
数学 78
语文 90
语文 80
语文 80
6 rows selected
二:rank------------------语文成绩70分的同学是排名第几。
select name,score,x.tt from (select name,score,rank() over(partition by name order by score desc) tt from t) x where x.name='语文' and x.score=70
NAME SCORE TT
---------- ----- ----------
语文 70 4
三：row_number——————分页查询
select xx.* from (select t.*,row_number() over(order by score desc) rowno from t) xx where xx.rowno between 1 and 3;
NAME SCORE ROWNO
---------- ----- ----------
数学 99 1
语文 90 2
数学 88 3
FIRST 功能描述：从DENSE_RANK返回的集合中取出排在最前面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录 SAMPLE：下面例子中DENSE_RANK按部门分区，再按佣金commission_pct排序，FIRST取出佣金最低的对应的所有行，然后前面的MAX函数从这个集合中取出薪水最低的值；LAST取出佣金最高的对应的所有行，
然后前面的MIN函数从这个集合中取出薪水最高的值 SELECT
department_id,
first_name||' '||last_name employee_name, hire_date, salary,
MIN(salary) KEEP (DENSE_RANK FIRST
ORDER BY hire_date) OVER (PARTITION BY department_id) "Worst",
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY hire_date) OVER (PARTITION BY
department_id) "Best"
FROM employees
LAST 功能描述：从DENSE_RANK返回的集合中取出排在最后面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录 SAMPLE：下面例子中DENSE_RANK按雇用日期排序，FIRST取出salary最低的对应的所有行，然后前面的MAX函数从这个集合中取出薪水最低的值；LAST取出雇用日期最高的对应的所有行，
然后前面的MIN函数从这个集合中取出薪水最高的值