rank,dense_rank,row_number使用和区别

朱雀 2021-02-13 16:22 621阅读 0赞

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