三张表联合查询并分组统计

[lshfong]

A表
id,name,userid
b表
id,name,userid
c表
id,name,userid
我想生成如下格式的数据
A表 B表 C表 全部共计
10 20 10 40

=============================
我现在只能分组统计一张表select count(*) as num,userid from A group by userid

[码无边]

select count(a.id) as aid,count(b.id) as bid,count(c.id) as cid,sum(aid+bid+cid) from a join b on a.userid=b.userid join c on b.userid=c.userid group by a.userid,b.userid,c.userid

[benluobo]

是统计总数还是每个userid的总数

[WWWWA]

select sum(if(bz='a',1,0)),sum(if(bz='b',1,0)),sum(if(bz='c',1,0)),count(*) from ( select 'a' as bz,* from a union all select 'b' as bz,* from b union all select 'c' as bz,* from c) a1 group by userid

[benluobo]

select tmp1.userid userid, ac countA, bc countB, ac+bc countAll from (select userid,count(id) ac from a group by userid) tmp1 inner join (select userid,count(id) bc from b group by userid) tmp2 on tmp1.userid = tmp2.userid

[benluobo]

select tmp1.userid userid, ac countA, bc countB, cc countC, ac+bc+cc countAll from (select userid,count(id) ac from a group by userid) tmp1 inner join (select userid,count(id) bc from b group by userid) tmp2 inner join (select userid,count(id) cc from c group by userid) tmp3 on tmp1.userid = tmp2.userid and tmp1.userid = tmp3.userid

[lshfong]

A表 id,name,userid b表 id,name,userid c表 id,name,userid 我想生成如下格式的数据 A表 B表 C表 全部共计 10 20 10 40 15 10 30 55 20 20 20 60 ====================== 统计要实现这种效果

[lshfong]

引用 5 楼 benluobobo 的回复:

select tmp1.userid userid, ac countA, bc countB, cc countC, ac+bc+cc countAll from (select userid,count(id) ac from a group by userid) tmp1 inner join (select userid,count(id) bc from b group by userid) tmp2 inner join (select userid,count(id) cc from c group by userid) tmp3 on tmp1.userid = tmp2.userid and tmp1.userid = tmp3.userid

这样统计很接近，但是显示不完，有些记录没显示出来

[benluobo]

哪些没有显示出来？

[lshfong]

userid同时在三个表中都有，才会显示 任何一个表中没有，最后统计出来的结果中就会没有

[lshfong]

用三楼的方法解决了，谢谢各位的热心解答

[benluobo]

那可以参考3楼的写法，不过需要改一个语法错误 select sum(if(bz='a',1,0)),sum(if(bz='b',1,0)),sum(if(bz='c',1,0)),count(*) from ( select 'a' as bz,a.* from a union all select 'b' as bz,b.* from b union all select 'c' as bz,c.* from c) a1 group by userid

[benluobo]

select sum(if(bz='a',1,0)),sum(if(bz='b',1,0)),sum(if(bz='c',1,0)),count(*) from ( select 'a' as bz,a.* from a union all select 'b' as bz,b.* from b union all select 'c' as bz,c.* from c) a1 group by userid