SQL怎么样按月同时按人汇总金额？


如图所示，在一个Table里边，有应收款ID(new_gatheringid)、销售人员ID(systemuserid)、销售人员名字(fullname)、应收款时间(new_gatheringdate)和应收款金额(new_gatheringmoney)。

现在要更新该表中的字段“TotalYingshou”，更新的规则是：
TotalYingshou是某个销售人员在每个月内的应收款金额（new_gatheringmoney）的总额。比如8月有两条记录的new_gatheringmoney分别是100、200￥，那么TotalYingshou = 100 + 200 = 300.

被这个问题困扰了很久。我之前用的是这样的一个SQL语句来创建该表，但明显的有问题：
Insert into .....
(SELECT Sum(New_gathering.New_gatheringmoney) 
FROM New_gathering INNER JOIN systemuser ON systemuser.systemuserid = New_gathering.ownerid
WHERE DateAdd(Hour,8,New_gathering.new_gatheringdate) >= '2010-8-1'
AND DateAdd(Hour,8,New_gathering.new_gatheringdate) < '2010-9-1'
AND New_gathering.ownerid = New_gatheringBase.OwningUser
Group By ownerid
) AS TotalYingshou
......

后来就想先不管该字段的值，在Create完了后再去Update，用的是下边的SQL：
UPDATE GZZG_Totalshouldcharge set totalshouldcharge=
(SELECT Sum(B.New_gatheringmoney) FROM GZZG_Totalshouldcharge AS B 
WHERE (@Date_min<=DATEADD(Hour, 8, B.New_gatheringdate)) AND (DATEADD(Hour, 8, B.New_gatheringdate)<@Date_max)
GROUP BY B.New_gatheringdate
)
WHERE id = B.id

会提示出错：无法绑定由多个部分组成的标识符 "B.id"



请高手帮忙解决下，小弟感激不尽。。

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

SQL code

UPDATE a set a.totalshouldcharge=
(SELECT Sum(B.New_gatheringmoney) FROM GZZG_Totalshouldcharge AS B  
WHERE datediff(mm,B.New_gatheringdate,a.New_gatheringdate)=0
and a.systemuserid = B.systemuserid
)
FROM GZZG_Totalshouldcharge a

------解决方案--------------------
SQL code

UPDATE a set totalshouldcharge= b.New_gatheringmoney
from GZZG_Totalshouldcharge a
join
(
   SELECT id,Sum(B.New_gatheringmoney) as New_gatheringmoney  FROM GZZG_Totalshouldcharge B 
   WHERE (@Date_min<=DATEADD(Hour, 8, B.New_gatheringdate)) AND (DATEADD(Hour, 8, B.New_gatheringdate)<@Date_max)
   GROUP BY B.New_gatheringdate
)t
WHERE t.id = a.id

------解决方案--------------------
探讨

引用:
try
SQL code
UPDATE a set a.totalshouldcharge=
(SELECT Sum(B.New_gatheringmoney) FROM GZZG_Totalshouldcharge AS B
WHERE datediff(mm,B.New_gatheringdate,a.New_gatheringdate)=0
……


------解决方案--------------------
SQL code
UPDATE a set a.totalshouldcharge=
(SELECT Sum(B.New_gatheringmoney) FROM GZZG_Totalshouldcharge AS B  
WHERE datediff(mm,B.New_gatheringdate,a.New_gatheringdate)=0
and a.systemuserid = B.systemuserid
)
FROM GZZG_Totalshouldcharge a