SQL中用Join 进行SUM时令我困惑的问题

[sunmoonn]

表A 表B 表C
ID Name Name Count Name Count
1 笔 笔 2 笔 -1
笔 -1


当我用下面的SQL进行SUM时
select A.Name, SUM(B.Count)as B_Count,SUM(C.Count) as C_Count
from A left outer join B on A.Name=B.Name
left outer join C on A.Name=C.Name
Group by A.Name

/*-----结果-------*/
Name B_Count C_Count
笔 4 -2

各位大虾
我这条SQL怎么改才正确能啊
正确应为
Name B_Count C_Count
笔 2 -2

[WangZWang]

select A.Name, SUM(B.Count)as B_Count,SUM(x.Count) as C_Count
from A left join B on A.Name=B.Name
left join (Select name,count=sum(count) from C
group by name) as x on A.Name=x.Name
Group by A.Name

[wgsasd311]

select A.Name, COUNT(B.Count)as B_Count,SUM(C.Count) as C_Count
from A left outer join B on A.Name=B.Name
left outer join C on A.Name=C.Name
Group by A.Name

[wgsasd311]

--不好意思,上面看错了,试试下面
select A.Name, SUM(B.Count)/COUNT(distinct c.count) as B_Count,SUM(C.Count) as C_Count
from A left outer join B on A.Name=B.Name
left outer join C on A.Name=C.Name
Group by A.Name

[lsqkeke]

declare @t1 table(id int,[name] varchar(5))
insert @t1
select 1,'笔'

declare @t2 table([name] varchar(5),[count] int)
insert @t2
select '笔',2

declare @t3 table([name] varchar(5),[count] int)
insert @t3
select '笔',-1 union all
select '笔',-1

select A.[Name],
B_Count=(select sum([count]) from @t2 where [name]=a.[name]),
C_Count=(select sum([count]) from @t3 where [name]=a.[name])
from @t1 a
group by [name]

笔 2 -2

[lsqkeke]

select [Name],
B_Count=isnull((select sum([count]) from B where [name]=A.[name]),0),
C_Count=isnull((select sum([count]) from C where [name]=A.[name]),0)
from A

[dashi111]

select A.name,
'b_count'=(select sum(B.count1) from B where B.name=A.name),
'c_count'=(select sum(C.count1) from C where C.name=A.name)
from A group by A.name

[sunmoonn]

lsqkeke(可可) 的非常正确
wgsasd311(自强不息) 应该能行
WangZWang(阿来) 改得我头晕(可能我比较差)

总之很感谢你们几位

[wgsasd311]

汗,
楼主,我的方法不对,没有仔细看清.可可方法很好.