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