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');
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;
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
---------- ---------- ---------- ---- ---------- ---------- ---------- ----------
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;
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
---------- ---------- ---------- ---- ---------- ---------- ---------- ----------
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行。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28344546/viewspace-751499/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28344546/viewspace-751499/