Update语句中使用聚合函数Sum

表warehousestorage

WarehouseID	Amount
1	100
1	100
1	100
1	100
1	100
1	100
1	100
1	100
1	28
1	0
1	100
4	14788
4	100
4	100
4	100
4	100
4	100
4	100d
1	100

表warehouse

WarehouseID	UsingCapacity
1	1028
2	0
3	0
4	15388
5	0

表warehouse中的 UsingCapacity是表warehousestorage对应项的累加值

SQL语句：
update warehouse set UsingCapacity = c.amount
from (select Sum(amount) as 'amount' , warehouseid from warehousestorage group by warehouseid) c, warehouse
where warehouse.warehouseid = c.warehouseid

运行后,统计值正确。不过又发现一个问题，因为表 warehousestorage累加后，只有2项，而表warehouse有5项
，上述代码只更新了WareHouseID=1,4的2项，其他并没有更新。
所以代码需要修改：
update warehouse set UsingCapacity = d.amount
from
(
 select wh.warehouseid, isnull(c.amount,0) as 'amount' from
 (select Sum(amount) as 'amount' , warehouseid from warehousestorage group by warehouseid) c right join warehouse wh
 on c.warehouseid = wh.warehouseid
) d,warehouse
where warehouse.warehouseid = d.warehouseid

修改后，可以正常更新其余的几项。

转载于:https://www.cnblogs.com/yushiro/archive/2006/09/20/509793.html