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
---------- -----
语文????????? 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 des