oracle库中的keep,ORACLE中的KEEP()使用方法

创建测试数据表 create table test(ID int ,MC int,SL int); insert into test values(1,111,1); insert into test values(1,222,1); insert into test values(1,333,2); insert into test values(1,555,3); insert into test values(1,666,3); insert into test values(2,111,1); insert into test values(2,222,1); insert into test values(2,333,2); insert into test values(2,555,2); commit; select * from test;

select id, mc, sl, min(mc) keep(DENSE_RANK first ORDER BY sl) over(partition by id) as min_mc_first, max(mc) keep(DENSE_RANK last ORDER BY sl) over(partition by id) as max_mc_last from test;

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

max是可以对应first的select id, mc, sl, min(mc) keep(DENSE_RANK first ORDER BY sl) over(partition by id) as min_first, max(mc) keep(DENSE_RANK first ORDER BY sl) over(partition by id) as max_first, min(mc) keep(DENSE_RANK last ORDER BY sl) over(partition by id) as min_last, max(mc) keep(DENSE_RANK last ORDER BY sl) over(partition by id) as max_last from test

对于id=1的结果集进行一下解释

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) 就是555，取max(mc)就是666

id=2的结果集同理