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(
    name VARCHAR(10),
     score NUMERIC(3)
 
 
SQL> insert into t(name,score)
     select '语文',60   union all
     select '语文',90   union all
     select '语文',80   union all
     select '语文',80   union all
     select '数学',67   union all
     select '数学',77   union all
     select '数学',78   union all
     select '数学',88   union all
    select '数学',99   union all
    select '语文',70 

 
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