with t as( select '1' id,'n1' name,0 value from dual union all select '2','n1',0 from dual union all select '3','n1',0 from dual union all select '1','n2',2000 from dual union all select '2','n3',4500 from dual union all select '3','n4',3000 from dual ), t1 as( select id,sum(value) "SUM" from t group by id ), t2 as( select name,sum(value) "SUM" from t group by name ) select name, (case "SUM" when 0 then (select sum("SUM") from t1 where id in (select id from t where name=t2.name)) else "SUM" end) "SUM" from t2; /* NAME SUM ---- ---------------------- n3 4500 n4 3000 n1 9500 n2 2000 */
select t.name, sum(t.value) as value from ( select t1.id, t1.name, t1.value from test t1 union all select t2.id, t2.name, sum(t2.value) as value from test t2 where exists (select 1 from test t3 where t3.id=t2.id and t3.name=t2.name and t3.value='0' ) group by t2.id, t2.name ) t group by t.name;
-- 正确的方法:select t1.name, decode(t1.value,0,sum(t2.value),t1.value) as value from test t1 left join test t2 on t1.id=t2.id and t1.name<>t2.name group by t1.name, t1.value;
with t as(
select '1' id,'n1' name,0 value from dual
union all
select '2','n1',0 from dual
union all
select '3','n1',0 from dual
union all
select '1','n2',2000 from dual
union all
select '2','n3',4500 from dual
union all
select '3','n4',3000 from dual
),
t1 as(
select id,sum(value) "SUM" from t group by id
),
t2 as(
select name,sum(value) "SUM" from t group by name
)
select name,
(case "SUM" when 0
then (select sum("SUM") from t1 where id in (select id from t where name=t2.name))
else "SUM" end) "SUM"
from t2;
/*
NAME SUM
---- ----------------------
n3 4500
n4 3000
n1 9500
n2 2000
*/
select t1.id, t1.name, t1.value
from test t1
union all
select t2.id, t2.name, sum(t2.value) as value
from test t2
where exists (select 1 from test t3
where t3.id=t2.id
and t3.name=t2.name
and t3.value='0' )
group by t2.id, t2.name
) t
group by t.name;
from test t1 left join test t2 on t1.id=t2.id and t1.name<>t2.name
group by t1.name, t1.value;