ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle分析函数,keep and over解说

oracle分析函数,keep and over解说

原创 Linux操作系统 作者:fengzj 时间:2009-04-18 18:55:31 0 删除 编辑
今天又学到了一个新的分析函数了,over()函数早就接触过,但keep和over结合用还是第一次见,详情见下面内容了,呵~~

先看下面的测试例子:

--创建测试表

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/219982/viewspace-590897/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 多种日期格式:
请登录后发表评论 登录
全部评论

注册时间:2008-11-11

  • 博文量
    76
  • 访问量
    177201