还是 查询两个表，根据A表ID字段查出B表有A表记录个数问题

[wn520]

A表： B表 C表
id name ID Aid ID Aid

现在SQL我写成这样
select a1.id,a1.NAME,count(b1.id) as bc ,count(c1.id) as cc from (a as a1
LEFT join b as b1 on a1.id =b1.aid )
LEFT join c as c1 On a1.id=c1.aid
group by a1.id,A1.NAME
ID Name bc cc
1 123 3 0
2 1233 2 2
4 1234 0 0
5 34 0 1
43 43 0 1


现在出现个问题，在C表中Aid=2 的记录只有一条，结果它变成了B表差的数据一样为2了，

将C表Aid=2改为1 它和B表一样为3条记录了！
这是怎么回事啊？？？

[君_洛洛]

select a1.id,a1.NAME,B.BTOTAL as bc ,C.CTOTAL as cc from a as A
LEFT JOIN(
SELECT AID,COUNT(*) 'BTOTAL' FROM B GROUP BY AID
) B ON A.ID=B.AID
LEFT JOIN(
SELECT AID,COUNT(*) 'CTOTAL' FROM C GROUP BY AID
) C ON A.ID=C.AID

[sgtzzc]

select 
  a1.id,a1.NAME,
  ISNULL(B1.CNT,0) as bc ,
  ISNULL(C1.CNT,0) as cc 
from a as a1 
LEFT join (SELECT AID,COUNT(1) AS CNT FROM B GROUP BY AID) as b1 on a1.id =b1.aid  
LEFT join (SELECT AID,COUNT(1) AS CNT FROM C GROUP GY AID) as c1 On a1.id=c1.aid 

[君_洛洛]

再加上ISNULL

select a1.id,a1.NAME,ISNULL(B.BTOTAL,0) as bc ,ISNULL(C.CTOTAL,0) as cc from a as A
LEFT JOIN(
SELECT AID,COUNT(*) 'BTOTAL' FROM B GROUP BY AID
) B ON A.ID=B.AID
LEFT JOIN(
SELECT AID,COUNT(*) 'CTOTAL' FROM C GROUP BY AID
) C ON A.ID=C.AID

[--小F--]

建议楼主还是拿数据来说话 这样好空洞 好难想

[bancxc]

select a1.id,a1.NAME,count(b1.id) as bc ,(select count(c.id) from c where aid=a1.id) as cc from 
a as a1 LEFT    join b as b1 on a1.id =b1.aid
group by a1.id,A1.NAME 

[bancxc]

你看一下A和B联查的结果集 就知道了
select * from a as a1 LEFT join b as b1 on a1.id =b1.aid

上面这个结果 在和c联查

[wn520]

[Quote=引用 5 楼 bancxc 的回复:]
SQL codeselect a1.id,a1.NAME,count(b1.id)as bc ,(selectcount(c.id)from cwhere aid=a1.id)as ccfrom
aas a1LEFTjoin bas b1on a1.id=b1.aidgroupby a1.id,A1.NAME
[/Quote]
实现了！回复的都谢谢了！
Sql区感觉挺不错！挺愿意帮助人的！谢谢！

[wn520]

[Quote=引用 4 楼 fredrickhu 的回复:]
建议楼主还是拿数据来说话 这样好空洞 好难想
[/Quote]


我不是用了3个表 说明嘛！ 查询结果也写出来了啊！

[wn520]

[Quote=引用 3 楼 janely318 的回复:]
再加上ISNULL
SQL codeselect a1.id,a1.NAME,ISNULL(B.BTOTAL,0)as bc ,ISNULL(C.CTOTAL,0)as ccfrom aas ALEFTJOIN(SELECT AID,COUNT(*)'BTOTAL'FROM BGROUPBY AID
) BON A.ID=B.AIDLEFTJOIN(SELECT AID,COUNT(*)'CTOTAL'FROM CGROUPBY AID
) CON A.ID=C.AID
[/Quote]


这个也能实现！ 谢谢