如何计算成本，MSSQL2000版本，如何计算成本，MSSQL2000版本
商品表x_prouct
pro_id pro_name pro_cost(成本）
1 电视 0
2 冰箱 0
采购单表x_buy
buy_id buy_code buy_tally(审核状态，2表示已审核）
 1 B0001 2
 2 B0002 2
采购明细表x_buyitem
item_id item_amount item_price item_pro(与商品表的pro_id对应） item_buy(关联采购单的buy_code)
 1 5 15.00 1 B0001
 2 3 10.00 1 B0001
 3 2 12.00 2 B0002
采购退货单表x_buyout
buyout_id buyout_code buyout_tally(审核状态，2表示已审核）
 1 T0001 2
 2 T0002 2
采购退货明细表x_buyoutitem
item_id item_amount item_price item_pro(与商品表的pro_id对应） item_buyout(关联采购退货单的buyout_code)
 1 1 15.00 1 t0001
 2 1 10.00 1 t0001
 3 1 12.00 2 t0002

如果计算商品的成本，成本公式为：（采购金额-退货金额）/（采购数量-退货数量）且只计算审核的单据。
select *,realmoney=buymoney-buyoutmoney,realamount=buyamount-buyoutamount,cost=(buymoney-buyoutmoney)/(case when (buyamount-buyoutamount)=0 then 1 else (buyamount-buyoutamount) end) from (select a.pro_id,a.pro_code,a.pro_name,a.pro_spec,a.pro_cost,(select isnull(sum(convert(decimal(18,2),b.item_amount)*convert(decimal(18,2),b.item_price)),0) from x_buyitem b left join x_buy c on b.item_buy=c.buy_code where 1=1 and b.item_pro=a.pro_id and c.buy_tally='2') as buymoney,(select isnull(sum(convert(decimal(18,2),b.item_amount)),0) from x_buyitem b left join x_buy c on b.item_buy=c.buy_code where 1=1 and b.item_pro=a.pro_id and c.buy_tally='2') as buyamount,(select isnull(sum(convert(decimal(18,2),b.item_amount)*convert(decimal(18,2),b.item_price)),0) from x_buyoutitem b left join x_buyout c on b.item_buyout=c.buyout_code where 1=1 and b.item_pro=a.pro_id and c.buyout_tally='2') as buyoutmoney,(select isnull(sum(convert(decimal(18,2),b.item_amount)),0) from x_buyoutitem b left join x_buyout c on b.item_buyout=c.buyout_code where 1=1 and b.item_pro=a.pro_id and c.buyout_tally='2') as buyoutamount from x_product a )t
我用上面的语句可以得到成本，请问如何更新商品中的成本呢

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

SQL code

update 
  a
set
  pro_cost=b.cost
from
  x_prouct a,
 (select *,realmoney=buymoney-buyoutmoney,realamount=buyamount-buyoutamount,cost=(buymoney-buyoutmoney)/(case when (buyamount-buyoutamount)=0 then 1 else (buyamount-buyoutamount) end) from (select a.pro_id,a.pro_code,a.pro_name,a.pro_spec,a.pro_cost,(select isnull(sum(convert(decimal(18,2),b.item_amount)*convert(decimal(18,2),b.item_price)),0) from x_buyitem b left join x_buy c on b.item_buy=c.buy_code where 1=1 and b.item_pro=a.pro_id and c.buy_tally='2') as buymoney,(select isnull(sum(convert(decimal(18,2),b.item_amount)),0) from x_buyitem b left join x_buy c on b.item_buy=c.buy_code where 1=1 and b.item_pro=a.pro_id and c.buy_tally='2') as buyamount,(select isnull(sum(convert(decimal(18,2),b.item_amount)*convert(decimal(18,2),b.item_price)),0) from x_buyoutitem b left join x_buyout c on b.item_buyout=c.buyout_code where 1=1 and b.item_pro=a.pro_id and c.buyout_tally='2') as buyoutmoney,(select isnull(sum(convert(decimal(18,2),b.item_amount)),0) from x_buyoutitem b left join x_buyout c on b.item_buyout=c.buyout_code where 1=1 and b.item_pro=a.pro_id and c.buyout_tally='2') as buyoutamount from x_product a )t
)b
where
  a.pro_id=b.pro_id