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的结果集同理