ORACLE中的KEEP()使用方法

4h93l2ctw5

　　
　　
　　2种取值：
DENSE_RANK FIRST
DENSE_RANK LAST
　　SQL>select*fromtest;

ID MC SL
---------------------------------------- -------------------
11111
12221
13332
15553
16663
21111
22221
23332
25552

9rowsselected

SQL>
SQL>selectid,mc,sl,
2min(mc) keep(DENSE_RANK firstORDERBYsl)over(partitionbyid),
3max(mc) keep(DENSE_RANK lastORDERBYsl)over(partitionbyid)
4fromtest
5;

ID MC SLMIN(MC)KEEP(DENSE_RANKFIRSTORDMAX(MC)KEEP(DENSE_RANKLASTORDE
---------------------------------------- ------------------- ------------------------------------------------------------
11111111666
12221111666
13332111666
15553111666
16663111666
21111111555
22221111555
23332111555
25552111555

9rowsselected

SQL>

不要混淆keep内（first、last）外（min、max或者其他）：
min是可以对应last的
max是可以对应first的
SQL>selectid,mc,sl,
2min(mc) keep(DENSE_RANK firstORDERBYsl)over(partitionbyid),
3max(mc) keep(DENSE_RANK firstORDERBYsl)over(partitionbyid),
4min(mc) keep(DENSE_RANK lastORDERBYsl)over(partitionbyid),
5max(mc) keep(DENSE_RANK lastORDERBYsl)over(partitionbyid)
6fromtest
7;

ID MC SLMIN(MC)KEEP(DENSE_RANKFIRSTORDMAX(MC)KEEP(DENSE_RANKFIRSTORDMIN(MC)KEEP(DENSE_RANKLASTORDEMAX(MC)KEEP(DENSE_RANKLASTORDE
---------------------------------------- ------------------- ------------------------------------------------------------ ------------------------------------------------------------
11111111222555666
12221111222555666
13332111222555666
15553111222555666
16663111222555666

21111111222333555
22221111222333555
23332111222333555
25552111222333555
　　对于id=1的结果集进行一下解释
min(mc) keep(DENSE_RANK firstORDERBYsl)over(partitionbyid)：id等于1的数量最小的（DENSE_RANKfirst ）为
11111
12221
在这个结果中取min(mc) 就是111
max(mc) keep(DENSE_RANK firstORDERBYsl)over(partitionbyid)
取max(mc) 就是222；
min(mc) keep(DENSE_RANK lastORDERBYsl)over(partitionbyid)：id等于1的数量最大的（DENSE_RANKfirst ）为
15553
16663
　　在这个结果中取min(mc) 就是555，取max(mc)就是666
　　id=2的结果集同理