select t.name, sum(t.value) as value from (
select t1.id, t1.name, t1.value
from test t1
where t1.name='n1'
union all
select t2.id, 'n1', sum(t2.value) as value
from test t2
where exists (select 1 from test t3
where t3.id=t2.id
and t3.name='n1'
and t3.value='0' )
group by t2.id
) t
where t.name='n1'
group by t.name;
select t1.id, t1.name, t1.value
from test t1
where t1.name='n1'
union all
select t2.id, 'n1', sum(t2.value) as value
from test t2
where exists (select 1 from test t3
where t3.id=t2.id
and t3.name='n1'
and t3.value='0' )
group by t2.id
) t
where t.name='n1'
group by t.name;
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;
--确实是伤脑筋额,做了个把小时。
SQL> edi
ÒÑдÈë file afiedt.buf 1 select b.name,
2 case when b.value=0
3 then (select sum(value) value from test where id in(select id from test where name=b.name))
4 else b.value end 汇总
5 from (select name,sum(value) value from test group by name) b
6* order by 2
SQL> /NAME 汇总
-------------------- ----------
n2 2000
n4 3000
n3 4500
n1 9500
SELECT NAME FROM test GROUP BY name
HAVING SUM(VALUE)=0 ) a,test b,test c
WHERE a.name=b.name AND b.id=c.id
GROUP BY a.name
HAVING SUM(c.value)<>0
Table created
SQL> insert into test values('1','n1',0);
1 row inserted
SQL> insert into test values('2','n1',0);
1 row inserted
SQL> insert into test values('3','n1',0);
1 row inserted
SQL> insert into test values('1','n2',2000);
1 row inserted
SQL> insert into test values('1','n3',4500);
1 row inserted
SQL> insert into test values('1','n4',3000);
1 row inserted
SQL>
SQL> SELECT a.name,SUM(c.value) FROM (
2 SELECT NAME FROM test GROUP BY name
3 HAVING SUM(VALUE)=0 ) a,test b,test c
4 WHERE a.name=b.name AND b.id=c.id
5 GROUP BY a.name
6 HAVING SUM(c.value)<>0
7 ;
NAME SUM(C.VALUE)
-------------------- ------------
n1 9500
SQL>
-- 直接根据意思来翻译成sql,也许容易理解些?
-- 第一部分是sum(value)<> 0 的,第二部分是汇总为0的
select id
,sum(value) value
from test
group by id
having sum(value) <> 0
union all -- 并起来
select a.name
,sum(b.value) value
from test a
inner join b
on a.id = b.id
where a.value = 0
group by a.name
-- 直接根据意思来翻译成sql,也许容易理解些?
-- 第一部分是sum(value)<> 0 的,第二部分是汇总为0的
select name
,sum(value) value
from test
group by name
having sum(value) <> 0
union all -- 并起来
select a.name
,sum(b.value) value
from test a
inner join b
on a.id = b.id
where a.value = 0
group by a.name
-- !-! 又贴少了,再贴!
select name
,sum(value) value
from test
group by name
having sum(value) <> 0
union all -- 并起来
select a.name
,sum(b.value) value
from test a
inner join test b
on a.id = b.id
where a.value = 0
group by a.name
select name,sum(value) from
(select a.name,c.value from
(
select name,sum(value) vlu,count(1) Cnt from test
group by name
having count(1)>1
) a left join
test b on a.name=b.name
left join test c
on b.id=c.id
) group by nameunion all
select name,sum(value) from test
group by name
having count(1)=1