oracle分析函式,keep and over解說

今天又學到了一個新的分析函式了，over（）函式早就接觸過，但keep和over結合用還是第一次見，詳情見下面內容了，呵~~[@more@]

先看下面的測試例子:

--建立測試表

create table test as
select 1 ID, 111 mc, 1 sl from dual union
select 1, 222, 1 from dual union
select 1, 333, 2 from dual union
select 1, 555, 3 from dual union
select 1, 666, 3 from dual union
select 2 ,111 ,1 from dual union
select 2 ,222 ,1 from dual union
select 2 ,333, 2 from dual union
select 2 ,555, 2 from dual;

SQL> select * from test;

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

已選擇9行。

SQL> select id,mc,sl,min(mc) keep(dense_rank first order by sl) over(partition by id) min_mc
2 from test;

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

已選擇9行。

SQL> select id,mc,sl,min(mc) keep(dense_rank last order by sl) over(partition by id) min_mc
2 from test;

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

已選擇9行。

SQL> select id,mc,sl,min(mc) keep(dense_rank first order by sl) over(partition by id) min_first_mc
2 ,max(mc) keep(dense_rank first order by sl) over(partition by id) max_first_mc,
3 min(mc) keep(dense_rank last order by sl) over(partition by id) min_last_mc,
4 max(mc) keep(dense_rank last order by sl) over(partition by id) max_last_mc
5 from test;

ID MC SL MIN_FIRST_MC MAX_FIRST_MC MIN_LAST_MC
---------- ---------- ---------- ------------ ------------ -----------
MAX_LAST_MC
-----------
1 111 1 111 222 555
666

1 222 1 111 222 555
666

1 333 2 111 222 555
666

ID MC SL MIN_FIRST_MC MAX_FIRST_MC MIN_LAST_MC
---------- ---------- ---------- ------------ ------------ -----------
MAX_LAST_MC
-----------
1 555 3 111 222 555
666

1 666 3 111 222 555
666

2 111 1 111 222 333
555

ID MC SL MIN_FIRST_MC MAX_FIRST_MC MIN_LAST_MC
---------- ---------- ---------- ------------ ------------ -----------
MAX_LAST_MC
-----------
2 222 1 111 222 333
555

2 333 2 111 222 333
555

2 555 2 111 222 333
555

已選擇9行。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21251711/viewspace-1019601/，如需轉載，請註明出處，否則將追究法律責任。