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
UPDATE a set a.totalshouldcharge=(SELECT Sum(B.New_gatheringmoney) FROM GZZG_Totalshouldcharge AS B WHERE datediff(mm,B.New_gatheringdate,a.New_gatheringdate)=0and a.systemuserid = B.systemuserid)FROM GZZG_Totalshouldcharge a
[解决办法]
UPDATE a set totalshouldcharge= b.New_gatheringmoneyfrom GZZG_Totalshouldcharge ajoin( 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)tWHERE t.id = a.id
[解决办法]
UPDATE a set a.totalshouldcharge=(SELECT Sum(B.New_gatheringmoney) FROM GZZG_Totalshouldcharge AS B WHERE datediff(mm,B.New_gatheringdate,a.New_gatheringdate)=0and a.systemuserid = B.systemuserid)FROM GZZG_Totalshouldcharge a
