求个数据统计的sql语句 sqlserver2008

[zamowhite]

tablea表
UserID UserName
1 admin
2 user
3 guest
tableb表
UesrID Score Score1 Score2
1 10.00 10.00 20.00
2 20.00 20.00 20.00
3 30.00 30.00 30.00
tablec表
UserID payamt1 payamt2
1 10.00 20.00
1 20.00 20.00
tabled表
UesrID cashamt1 cashamt2
2 10.00 -10.00
2 10.00 -10.00
2 10.00 -10.00

查询结果
UserID UserName Score Score1 Score2 payamt1 payamt2 cashamt1 cashamt2
1 admin 10.00 10.00 10.00 30.00 40.00 0.00 0.00
2 user 20.00 20.00 20.00 0.00 0.00 30.00 -30.00
3 gueset 30.00 30.00 30.00 0.00 0.00 0.00 0.00

select a.userid,a.username,b.score,b.score1,b.score2,
sum(c.payamt1) as payamt1,
sum(c.payamt2) as payamt2,
sum(d.cashamt1) as cashamt1,
sum(d.cashamt2) as cashamt2
FROM table a
inner join tableb  b on b.userid = a.userid
inner join tablec c on c.userid = b.userid
inner join tabled d on d.userid = c.userid
group by a.userid,a.username,b.score,b.score1,b.score2

我用这个语句sum出来的结果是有问题的，麻烦大神帮忙看看，要如何修改！

[中国风]

C和D没记录显示0时用ISNULL

select 
a.userid,a.username,b.score,b.score1,b.score2,
isnull(c.payamt1,0) as payamt1,
isnull(c.payamt2,0_) as payamt2,
isnull(d.cashamt1,0) as cashamt1,
isnull(d.cashamt2,0) as cashamt2
FROM tablea a
inner join tableb  b on b.userid = a.userid
left  join (select UserID,payamt1=SUM(payamt1),payamt2=SUM(payamt2) from tablec group by UserID) as c on c.userid = b.userid
left join (select UserID,cashamt1=SUM(cashamt1),cashamt2=SUM(cashamt2) from tabled group by UserID) as d on d.userid = c.userid

[zamowhite]

谢谢版主，要的就是个效果 。感激。。。

[li_shiye]

嗯，多表关联统计时，需要注意重复值的问题

[中国风]

select 
a.userid,a.username,b.score,b.score1,b.score2,
c.payamt1,
c.payamt2,
d.cashamt1,
d.cashamt2
FROM tablea a
inner join tableb  b on b.userid = a.userid
left  join (select UserID,payamt1=SUM(payamt1),payamt2=SUM(payamt2) from tablec group by UserID) as c on c.userid = b.userid
left join (select UserID,cashamt1=SUM(cashamt1),cashamt2=SUM(cashamt2) from tabled group by UserID) as d on d.userid = c.userid

[中国风]

先合计再用左联