oracle last over,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行。