with t1 as (
select 1 as value_col,'a' as name_col,'一' as group_col from dual
union all
select 2 as value_col,'b' as name_col,'一' as group_colfrom dual
union all
select 3 as value_col,'c' as name_col,'一' as group_colfrom dual
union all
select 0 as value_col,'d' as name_col,'一' as group_colfrom dual
union all
select 5 as value_col,'a' as name_col,'二' as group_col from dual
union all
select 6 as value_col,'b' as name_col,'二' as group_colfrom dual
union all
select 7 as value_col,'c' as name_col,'二' as group_colfrom dual
union all
select 9 as value_col,'d' as name_col,'二' as group_colfrom dual
)
--select sum(value_col) * decode(min(abs(sign(value_col))),0,1,0) ,group_col from t1 group by group_col
,
t2 as (
select value_col,name_col,group_col from t1 where value_col0 and name_col = 'd'
),
t3 as (
select
(case when t2.value_col0 then 0 else t1.value_col end) as value_col,
t1.name_col,
t1.group_col
from t1 left join t2 on t1.group_col = t2.group_col
)
select sum(t3.value_col),t3.group_col from t3 group by t3.group_col复制代码
|