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
修改后,可以正常更新其余的几项。