大家看看我的语句哪里有问题,谢谢


create   table   #t1
(
    count1   int,
    id   int
)
create   table   #t2
(
    count2   int,
    id   int
)

create   table   #ids
(
    id   int
)

insert   into   #ids   values(1)
insert   into   #ids   values(2)
insert   into   #ids   values(3)

insert   into   #t1   values(3,1)
insert   into   #t1   values(5,1)

insert   into   #t2   values(2,1)
insert   into   #t2   values(1,1)
insert   into   #t2   values(9,2)

我想按照id分组,对#t1count1字段进行求和,对#t2表的count2字段进行求和.sql语句如下:
select   #ids.id,isnull(sum(#t1.count1),0)   count1,isnull(sum(#t2.count2),0)   from   #ids  
left   join   #t1   on   #ids.id=#t1.id
left   join   #t2   on   #ids.id=#t2.id
group   by   #ids.id

帮忙


------解决方案--------------------

select #ids.id,isnull(sum(#t1.count1),0) count1,isnull(sum(#t2.count2),0) as count2
from #ids
left join (select id,sum(count1) as count1 from #t1 group by id)#t1 on #ids.id=#t1.id
left join (select id,sum(count2) as count2 from #t2 group by id)#t2 on #ids.id=#t2.id
group by #ids.id

--结果
1 8 3
2 0 9
3 0 0

------解决方案--------------------
因为2次join，会出现重复计算，如count1的3在1,3,2出现一次，1,3,1又出现了一次，你一起sum就会导致数据错误
要这样写：
select id,(select sum(count1) from #t1 where id=#ids.id) count1,(select sum(count2) from #t2 where id=#ids.id) count2 from #ids