最新消息:

rank,dense_rank,row_number使用和区别

SQL 观测者 1635浏览

一:语法(用法)
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(
name varchar2(10),
score number(3));

SQL> insert into t(name,score)
select '语文',60 from dual union all
select '语文',90 from dual union all
select '语文',80 from dual union all
select '语文',80 from dual union all
select '数学',67 from dual union all
select '数学',77 from dual union all
select '数学',78 from dual union all
select '数学',88 from dual union all
select '数学',99 from dual union all
select '语文',70 from dual

SQL> select * from t;

NAME SCORE
---------- -----
语文    60
语文    90
语文    80
语文    80
数学    67
数学    77
数学    78
数学    88
数学    99
语文    70

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

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

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

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

大家应该明白了吧!接下来看应用:

一: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 二: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

转载请注明:观测者 » rank,dense_rank,row_number使用和区别