函数---keep

Java代码  
KEEP   
  
看到很多人对于keep不理解，这里解释一下！   
  
  
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内（first、last）外（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  

补充：软件开发 , Java ,

上一个：函数---GROUPING
下一个：利用syscat.references递归查出他的所有关联表