1. DENSE_RANK()

 

create table denserank (aa integer,bb number(4),cc number (4),dd varchar2(4))
insert into denserank values(1,111,111,'a');
insert into denserank values(1,222,111,'a');
insert into denserank values(1,333,777,'b');
insert into denserank values(1,444,111,'b');
insert into denserank values(1,555,888,'a');
insert into denserank values(2,111,111,'a');
insert into denserank values(2,222,111,'a');
insert into denserank values(2,333,111,'b');
insert into denserank values(2,444,111,'a');
insert into denserank values(3,555,777,'c');
insert into denserank values(3,555,888,'c');
insert into denserank values(3,666,999,'c');
insert into denserank values(3,666,222,'c');
insert into denserank values(4,111,111,'c');
insert into denserank values(4,111,111,'c');
insert into denserank values(4,111,111,'c');

SQL> select aa,
  2         bb,
  3         cc,
  4         dd,
  5         min(bb) keep(DENSE_RANK first ORDER BY dd) over(partition by aa) minfirst,
  6         max(bb) keep(DENSE_RANK first ORDER BY dd) over(partition by aa) maxfirst,
  7         min(bb) keep(DENSE_RANK last ORDER BY dd) over(partition by aa) minlast,
  8         max(bb) keep(DENSE_RANK last ORDER BY dd) over(partition by aa) maxlast
  9    from denserank;

        AA         BB         CC DD     MINFIRST   MAXFIRST    MINLAST    MAXLAST
---------- ---------- ---------- ---- ---------- ---------- ---------- ----------
         1        222        111 a           111        555        333        444
         1        111        111 a           111        555        333        444
         1        333        777 b           111        555        333        444
         1        444        111 b           111        555        333        444
         1        555        888 a           111        555        333        444
         2        222        111 a           111        444        333        333
         2        111        111 a           111        444        333        333
         2        333        111 b           111        444        333        333
         2        444        111 a           111        444        333        333
         3        555        777 c           555        666        555        666
         3        555        888 c           555        666        555        666
         3        666        999 c           555        666        555        666
         3        666        222 c           555        666        555        666
         4        111        111 c           111        111        111        111
         4        111        111 c           111        111        111        111
         4        111        111 c           111        111        111        111

已选择16行。

 

SQL> select aa,bb,cc,dd,
  2         min(bb) keep(DENSE_RANK first ORDER BY dd desc ) over(partition by aa) minfirst,
  3         max(bb) keep(DENSE_RANK first ORDER BY dd desc ) over(partition by aa) maxfirst,
  4         min(bb) keep(DENSE_RANK last ORDER BY dd desc ) over(partition by aa) minlast,
  5         max(bb) keep(DENSE_RANK last ORDER BY dd desc ) over(partition by aa) maxlast
  6    from denserank;

        AA         BB         CC DD     MINFIRST   MAXFIRST    MINLAST    MAXLAST
---------- ---------- ---------- ---- ---------- ---------- ---------- ----------
         1        222        111 a           333        444        111        555
         1        111        111 a           333        444        111        555
         1        333        777 b           333        444        111        555
         1        444        111 b           333        444        111        555
         1        555        888 a           333        444        111        555
         2        222        111 a           333        333        111        444
         2        111        111 a           333        333        111        444
         2        333        111 b           333        333        111        444
         2        444        111 a           333        333        111        444
         3        555        777 c           555        666        555        666
         3        555        888 c           555        666        555        666
         3        666        999 c           555        666        555        666
         3        666        222 c           555        666        555        666
         4        111        111 c           111        111        111        111
         4        111        111 c           111        111        111        111
         4        111        111 c           111        111        111        111

已选择16行。