Dear All:
遇到一个sql对2列求最小值。不希望通过2个子查询来实现,请教大家有没有更好的方法。具体如下:
测试表:
col1 va1 val2
A 1 1
A 0 0
A 1.23 0.1
A 5 5
A -0.32
A 2.3 -4.5按col1分组,求val1和val2中大于0的最小值.期望结果为:col1 va1 val2
A 1 0.1
我只想到用2个子查询来找出val1和val2各自大于0的最小值,请教有没有更好方法?? ...
(select well_id,min(val1) from test1 where val1>0
group by well_id)
...
(select well_id,min(val2) from test1 where val2>0
group by well_id)
遇到一个sql对2列求最小值。不希望通过2个子查询来实现,请教大家有没有更好的方法。具体如下:
测试表:
col1 va1 val2
A 1 1
A 0 0
A 1.23 0.1
A 5 5
A -0.32
A 2.3 -4.5按col1分组,求val1和val2中大于0的最小值.期望结果为:col1 va1 val2
A 1 0.1
我只想到用2个子查询来找出val1和val2各自大于0的最小值,请教有没有更好方法?? ...
(select well_id,min(val1) from test1 where val1>0
group by well_id)
...
(select well_id,min(val2) from test1 where val2>0
group by well_id)
with t as(
select 'A' col_1,1 col_2,1 col_3 from dual union all
select 'A',0,0 from dual union all
select 'A',1.23,0.1 from dual union all
select 'A',5,5 from dual union all
select 'A',-0.32,null from dual union all
select 'A',2.3,-4.5 from dual)
--方法一:比较麻烦
select col_1,max(decode(rn1,1,col_2)) col_2,max(decode(rn2,1,col_3)) col_3
from
(select col_1,col_2,col_3,row_number() over (partition by col_1 order by col_2 asc) rn1,
row_number() over (partition by col_1 order by col_3 asc) rn2
from t
where col_2 >0
and col_3 >0)
group by col_1
/
--方法二:比较简单
select col_1,min(col_2),min(col_3)
from t
where col_2 > 0
and col_3 >0
group by col_1
/
SELECT MIN(CASE WHEN VAL1>0 THEN VAL1 END) VAL1,
MIN(CASE WHEN VAL2>0 WHEN VAL2 END) VAL2
FORM TEST1
GROUP BY COLL