ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle中分组查询时keep的使用[转][

Oracle中分组查询时keep的使用[转][

原创 Linux操作系统 作者:47328983 时间:2013-02-07 10:59:21 0 删除 编辑

for   example:  
   
  15:18:59   SQL>   create   table   t1   (a   int,   b   int);  
   
  表已创建。  
   
  已用时间:     00:   00:   00.15  
  15:18:59   SQL>  
  15:18:59   SQL>   insert   into   t1   values   (1,5);  
   
  已创建   1   行。  
   
  已用时间:     00:   00:   00.16  
  15:18:59   SQL>   insert   into   t1   values   (1,3);  
   
  已创建   1   行。  
   
  已用时间:     00:   00:   00.16  
  15:18:59   SQL>   insert   into   t1   values   (1,4);  
   
  已创建   1   行。  
   
  已用时间:     00:   00:   00.15  
  15:18:59   SQL>   insert   into   t1   values   (2,1);  
   
  已创建   1   行。  
   
  已用时间:     00:   00:   00.16  
  15:18:59   SQL>   insert   into   t1   values   (2,3);  
   
  已创建   1   行。  
   
  已用时间:     00:   00:   00.00  
  15:18:59   SQL>  
  15:18:59   SQL>   SELECT  
  15:18:59       2     max(b)   KEEP   (DENSE_RANK   LAST   ORDER   BY   (a)   desc)   AS   last,  
  15:18:59       3     max(b)   KEEP   (DENSE_RANK   FIRST   ORDER   BY   (a)   desc)   AS   first  
  15:18:59       4     FROM   t1;  
   
              LAST             FIRST  
  ----------   ----------  
                    5                     3  
   
  已用时间:     00:   00:   00.00

SQL含义:  

  从DENSE_RANK取出的结果集中取出最小的一项(以a排序),可能取出多行,因为a值可能相同。再从这些行中取出b字段最大的一行。
  选出  
  以字段a分组,最小一组中(LAST   降序)所有记录中b的最大值  
  和  
  以字段a分组,最大一组中(FRIST   降序)所有记录中b的最大值

Returns the row ranked first using DENSE_RANK
2种取值:
DENSE_RANK FIRST
DENSE_RANK LAST

keep (DENSE_RANK first
ORDER BY sl) 结果集中再取max、min的例子。


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 rows selected

SQL
>
SQL
> select id,mc,sl,
2 min(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id),
3 max(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id)
4 from test
5 ;

ID MC SL
MIN(MC)KEEP(DENSE_RANKFIRSTORD MAX(MC)KEEP(DENSE_RANKLASTORDE
-------------------- -------------------- ------------------- ------------------------------ ------------------------------
1 111 1 111 666
1 222 1 111 666
1 333 2 111 666
1 555 3 111 666
1 666 3 111 666
2 111 1 111 555
2 222 1 111 555
2 333 2 111 555
2 555 2 111 555

9 rows selected

SQL
>

不要混淆keep内(firstlast)外(
min、max或者其他):
min是可以对应last
max是可以对应first

SQL
> select id,mc,sl,
2 min(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id),
3 max(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id),
4 min(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id),
5 max(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id)
6 from test
7 ;

ID MC SL
MIN(MC)KEEP(DENSE_RANKFIRSTORD MAX(MC)KEEP(DENSE_RANKFIRSTORD MIN(MC)KEEP(DENSE_RANKLASTORDE MAX(MC)KEEP(DENSE_RANKLASTORDE
-------------------- -------------------- ------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
1 111 1 111 222 555 666
1 222 1 111 222 555 666
1 333 2 111 222 555 666
1 555 3 111 222 555 666
1 666 3 111 222 555 666
2 111 1 111 222 333 555
2 222 1 111 222 333 555
2 333 2 111 222 333 555
2 555 2 111 222 333 555

9 rows selected


SQL
> select id,mc,sl,
2 min(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id),
3 max(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id),
4 min(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id),
5 max(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id)
6 from test
7 ;

ID MC SL
MIN(MC)KEEP(DENSE_RANKFIRSTORD MAX(MC)KEEP(DENSE_RANKFIRSTORD MIN(MC)KEEP(DENSE_RANKLASTORDE MAX(MC)KEEP(DENSE_RANKLASTORDE
-------------------- -------------------- ------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
1 111 1 111 222 555 666
1 222 1 111 222 555 666
1 333 2 111 222 555 666
1 555 3 111 222 555 666
1 666 3 111 222 555 666

2 111 1 111 222 333 555
2 222 1 111 222 333 555
2 333 2 111 222 333 555
2 555 2 111 222 333 555

min(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id):id等于1的数量最小的(DENSE_RANK first )为
1 111 1
1 222 1
在这个结果中取min(mc) 就是111
max(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id)
取max(mc) 就是222;
min(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id):id等于1的数量最大的(DENSE_RANK first )为
1 555 3
1 666 3
在这个结果中取min(mc) 就是222,取max(mc)就是666

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/241379/viewspace-753989/,如需转载,请注明出处,否则将追究法律责任。

上一篇: db2 错误小结-1
下一篇: DB2存储过程
请登录后发表评论 登录
全部评论

注册时间:2009-03-07

  • 博文量
    111
  • 访问量
    331745