Access多表统计问题

[jacky00yyp]

表A
id a
1 a1
2 a2
3 a3

表B
b aid
b1 1
b2 1
b3 2

表C
c aid
c1 3
c2 3
c3 1

求SQL语句来统计b和C表的aid,对应a表的个数 结果为
表A
id a countB countC
1 a1 2 1
2 a2 1 0
3 a3 0 3

[wwwwb]

SELECT a.*,
(select count(*) from bb1 where a.id=aid),(select count(*) from bc1 where a.id=aid)
from ba1 a

[wwwwb]

or
select a.a,nz(bb,0),nz(cc,0) from (ba1 a left join
(select aid,count(*) as bb from bb1 group by aid) b
on a.id=b.aid)
left join
(select aid,count(*) as cc from bc1 group by aid) c
on a.id=c.aid

[中国风]

select t1.ID,t1.b_aid,t2.c_aid
from
(select a.ID,count(b.aid) as b_aid from a left join b on a.ID=b.aid group by a.ID)t1,
(select a.ID,count(c.aid) as c_aid from a left join c on a.ID=c.aid group by a.ID)t2
where
t1.id=t2.id

[ewang11]

SELECT 表A.id, 表A.a, nz(e.aid计数,0) AS countB, nz(F.aid计数,0) AS countC
FROM (表A LEFT JOIN (SELECT 表b.aid, Count(*) AS aid计数 FROM 表b GROUP BY 表b.aid) e ON 表A.id=e.aid) LEFT JOIN (SELECT 表c.aid, Count(*) AS aid计数 FROM 表c GROUP BY 表c.aid) f ON 表A.id=f.aid