表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
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
你好,如果我想再关联两张表该如何写啊
表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 测试 自己
,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.comsidgroup by comid,coms.names,users.names