创建测试数据表
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的结果集同理
标签：DENSE,mc,min,KEEP,test,ORACLE,方法,id,first
来源： https://blog.csdn.net/xiongfei0131/article/details/90243174