oracle row_number() over ,rank() over ,dense_rank() over ，keep 函数比较

create table test2 as
select 1 id, 111 mc,1 sl from dual union all
select 1 , 222,6 from dual union all
select 1 , 333,2 from dual union all
select 1 , 555,3 from dual union all
select 1 , 666,3 from dual union all
select 2 , 111,1 from dual union all
select 2 , 222,1 from dual union all
select 2 , 333,2 from dual union all
select 2 , 555,2 from dual

select t.* ,
row_number() over (partition by id order by t.sl) row_num,
rank() over (partition by id order by t.sl) rank,
dense_rank() over (partition by id order by t.sl) denserank
from test2 t

        ID         MC         SL    ROW_NUM       RANK DENSERANK
---------- ---------- ---------- ---------- ---------- ----------
         1        111          1          1          1          1
         1        333          2          2          2          2
         1        666          3          3          3          3
         1        555          3          4          3          3
         1        222          6          5          5          4
         2        111          1          1          1          1
         2        222          1          2          1          1
         2        555          2          3          3          2
         2        333          2          4          3          2

可知：

dense_rank在做排序时如果遇到列有重复值，则重复值所在行的序列值相同，而其后的序列值依旧递增，rank则是重复值所在行的序列值相同，但其后的序列值从+重复行数开始递增，而row_number则不管是否有重复行，(分组内)序列值始终递增

keep（dense_rank first/last order by ）

select id,mc,sl,
2 min(mc) keep(dense_rank first order by sl) over(partition by id) min_mc,
3 min(mc) keep(dense_rank last order by sl) over(partition by id) min_mc
4   from test2;

        ID         MC         SL     MIN_MC     MIN_MC
---------- ---------- ---------- ---------- ----------
         1        111          1        111        222
         1        222          6        111        222
         1        333          2        111        222
         1        555          3        111        222
         1        666          3        111        222
         2        111          1        111        333
         2        222          1        111        333
         2        333          2        111        333
         2        555          2        111        333

9 rows selected

 

 

转自:

http://blog.163.com/dykj_dxj/blog/static/2549252520108271158241/