oracle库中的keep,ORACLE中的KEEP()使用方法-优快云博客

创建了一个名为test的测试数据表，并插入了多条记录，然后通过SQL查询获取每个ID对应的MC最小值（min_mc_first）和最大值（max_mc_last）。DENSE_RANK函数用于确定SL字段的顺序，KEEP子句结合ORDER BY用于在每个ID分区中找到第一和最后一个MC值。查询结果解释表明，该查询旨在分析ID下的MC值分布。

创建测试数据表 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