# rank(),dense_rank(),row_number()函数区别

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()       是跳跃拍学，两个第二名下来就是第四名

理论就不多讲了，看了案例，一下就明白了

SQL> create table t(
2   name varchar2(10),
3   score number(3));

Table created

SQL> insert into t(name,score)
2   select '语文',60 from dual union all
3   select '语文',90 from dual union all
4   select '语文',80 from dual union all
5   select '语文',80 from dual union all
6   select '数学',67 from dual union all
7   select '数学',77 from dual union all
8   select '数学',78 from dual union all
9   select '数学',88 from dual union all
10   select '数学',99 from dual union all
11   select '语文',70 from dual
12  /

10 rows inserted

SQL> select * from t;

NAME       SCORE
---------- -----

10 rows selected

SQL> select name,score,rank() over(partition by name order by score) tt from t;

NAME       SCORE         TT
---------- ----- ----------

10 rows selected

SQL> select name,score,dense_rank() over(partition by name order by score) tt from t;

NAME       SCORE         TT
---------- ----- ----------

10 rows selected

SQL> select name,score,row_number() over(partition by name order by score) tt from t;

NAME       SCORE         TT
---------- ----- ----------

10 rows selected

SQL> select name,score,rank() over(order by score) tt from t;

NAME       SCORE         TT
---------- ----- ----------

10 rows selected

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
---------- -----

6 rows selected

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
---------- ----- ----------

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
---------- ----- ----------

• 广告
• 抄袭
• 版权
• 政治
• 色情
• 无意义
• 其他

120