函数---keep

KEEP看到很多人对于keep不理解，这里解释一下！Returns the row ranked first using DENSE_RANK2种取值：DENSE_RANK FIRST DENSE_RANK LAST 在keep (DENSE_RANK first ORDER BY sl) 结果集中再取max、min的例子。SQL> select * from test;ID MC SL-------------------- -------------------- -------------------1 111 11 222 11 333 21 555 31 666 32 111 12 222 12 333 22 555 29 rows selectedSQL> 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 test5 ;ID MC SL MIN(MC)KEEP(DENSE_RANKFIRSTORD MAX(MC)KEEP(DENSE_RANKLASTORDE-------------------- -------------------- ------------------- ------------------------------ ------------------------------1 111 1 111 6661 222 1 111 6661 333 2 111 6661 555 3 111 6661 666 3 111 6662 111 1 111 5552 222 1 111 5552 333 2 111 5552 555 2 111 5559 rows selectedSQL>不要混淆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 test7 ;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 6661 222 1 111 222 555 6661 333 2 111 222 555 6661 555 3 111 222 555 6661 666 3 111 222 555 6662 111 1 111 222 333 5552 222 1 111 222 333 5552 333 2 111 222 333 5552 555 2 111 222 333 5559 rows selectedSQL> 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 test7 ;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 6661 222 1 111 222 555 6661 333 2 111 222 555 6661 555 3 111 222 555 6661 666 3 111 222 555 6662 111 1 111 222 333 5552 222 1 111 222 333 5552 333 2 111 222 333 5552 555 2 111 222 333 555min(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id)：id等于1的数量最小的—ENSE_RANK first ）为1 111 1 1 222 1 在这个结果中取min(mc) 就是111max(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的数量最大的—ENSE_RANK first ）为1 555 3 1 666 3 在这个结果中取min(mc) 就是222，取max(mc)就是666