multiple-select - mysql 多表关联查询count返回0如何写select

Question

现在
表A数据
id name
0 aaa
1 bbb
2 ccc

表B数据
id detailAlpha
0 one
0 two
0 three
2 one
2 two

表C数据
id detailBeta
0 one
0 two
1 one
1 two

A.id=B.id & A.id=C.id
关联查询语句count如何写能够返回0

我想select的count数据是
id count(B.id) count(C.id)
0 3 2
1 0 2
2 2 0

Answer 1

select id1 id, bcount, ccount from
  (select A.id as id1, 
    sum(B.id is not null) as bcount 
    from A left join B using(id) group by id1) t1,
  (select A.id as id2, 
    sum(c.id is not null) as ccount 
    from A left join C using(id) group by id2) t2
where id1 = id2;

总体就是 @OhKam 的思路.

Answer 2

A表查询ID 左联接加入 临时表B和临时表C
（B表查询ID和ID统计）as temptableB
（C表查询ID和ID统计）as temptableC