表结构和数据
字段 codeA codeB a b
数据 01 AAA 2 100
01 BBB 3 200
01 CCC 4 300
02 AAA 2 900
02 BBB 3 800
01 BBB 3 700要求结果:
01 9 1300
02 5 1700 说明 汇总a时,codeB相同时,只计算一次,汇总B时,都计算
如何在一个语句中实现,请教各位高手了!
字段 codeA codeB a b
数据 01 AAA 2 100
01 BBB 3 200
01 CCC 4 300
02 AAA 2 900
02 BBB 3 800
01 BBB 3 700要求结果:
01 9 1300
02 5 1700 说明 汇总a时,codeB相同时,只计算一次,汇总B时,都计算
如何在一个语句中实现,请教各位高手了!
(select '01' codeA,'AAA' codeB,2 a,100 b from dual
union all select '01','BBB',3,200 from dual
union all select '01','CCC',4,300 from dual
union all select '02','AAA',2,900 from dual
union all select '02','BBB',3,800 from dual
union all select '01','BBB',3,700 from dual
)
select codeA,sum(a),sum(b) from
(select codeA,codeB,a,sum(b) b from tmp group by codeA,codeB,a)
group by codeA;
from tt
group by codea