mysql 分组求和

[opq8976712]

表A:
id comid uid cnt
1 1 1 1
2 1 2 3
3 1 3 5
表B:
id comid uid cnt
1 1 1 1
2 1 2 4
3 1 3 5
表C:
id comid uid cnt
1 1 1 1
2 1 2 5
3 1 3 5

求问:我想把这A.B.C这三张表,comid相同的cnt求和的sql该怎么写结果类似于:
comid a.cnt b.cnt c.cnt
1 9 10 11

[中国风]

select comid 
,sum(case when Flag='A' then cnt else 0 end)  AS Acnt
,sum(case when Flag='B' then cnt else 0 end)  AS Bcnt
,sum(case when Flag='C' then cnt else 0 end)  AS Ccnt
from 
(select *,'A' as Flag from A
union all
select *,'B' as Flag from B
union all
select *,'C' as Flag from C
) as T
group by comid

[ACMAIN_CHM]

h723836333 本版等级：T1 结帖率：14.29%

引用

当您的问题得到解答后请及时结贴. http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html http://topic.csdn.net/u/20100428/09/BC9E0908-F250-42A6-8765-B50A82FE186A.html http://topic.csdn.net/u/20100626/09/f35a4763-4b59-49c3-8061-d48fdbc29561.html 8、如何给分和结贴？ http://bbs.csdn.net/help#post_dispose

[文修]

楼主你好 一楼正解，楼主可以一试

[opq8976712]

引用 1 楼 roy_88 的回复:

select comid 
,sum(case when Flag='A' then cnt else 0 end)  AS Acnt
,sum(case when Flag='B' then cnt else 0 end)  AS Bcnt
,sum(case when Flag='C' then cnt else 0 end)  AS Ccnt
from 
(select *,'A' as Flag from A
union all
select *,'B' as Flag from B
union all
select *,'C' as Flag from C
) as T
group by comid

你好,如果我想再关联两张表该如何写啊 表coms: id names 1 测试 表users: id names comsid 10 自己 1 想要的结果 comid a.cnt b.cnt c.cnt coms.names users.names 1 9 10 11 测试 自己

[LongRui888]

这么改就行：

select comid 
,sum(case when Flag='A' then cnt else 0 end)  AS Acnt
,sum(case when Flag='B' then cnt else 0 end)  AS Bcnt
,sum(case when Flag='C' then cnt else 0 end)  AS Ccnt
,coms.names
,users.names
from 
(select *,'A' as Flag from A
union all
select *,'B' as Flag from B
union all
select *,'C' as Flag from C
) as T
left join coms on coms.id = T.comid
left join users on users.id = T.comsid

group by comid,coms.names,users.names