怎么计算单位成本？怎么计算单位成本

--> 测试数据：[x_pro]if object_id('[x_pro]') is not null drop table [x_pro]create table [x_pro]([pro_id] int,[pro_name] varchar(4),[pro_cost] int)insert [x_pro]select 1,'电视',0 union allselect 2,'冰箱',0--> 测试数据：[x_buy]if object_id('[x_buy]') is not null drop table [x_buy]create table [x_buy]([buy_id] int,[buy_code] varchar(5),[buy_tally] int)insert [x_buy]select 1,'B0001',2 union allselect 2,'B0002',2--> 测试数据：[x_buyitem]if object_id('[x_buyitem]') is not null drop table [x_buyitem]create table [x_buyitem]([item_id] int,[item_amount] int,[item_price] numeric(5,2),[item_pro] int,[item_buy] varchar(5))insert [x_buyitem]select 1,5,15.00,1,'B0001' union allselect 2,3,10.00,1,'B0001' union allselect 3,2,12.00,2,'B0002'--> 测试数据：[x_buyout]if object_id('[x_buyout]') is not null drop table [x_buyout]create table [x_buyout]([buyout_id] int,[buyout_code] varchar(5),[buyout_tally] int)insert [x_buyout]select 1,'T0001',2 union allselect 2,'T0002',2--> 测试数据：[x_buyoutitem]if object_id('[x_buyoutitem]') is not null drop table [x_buyoutitem]create table [x_buyoutitem]([item_id] int,[item_amount] int,[item_price] numeric(4,2),[item_pro] int,[item_buyout] varchar(5))insert [x_buyoutitem]select 1,1,15.00,1,'t0001' union allselect 2,1,10.00,1,'t0001' union allselect 3,1,12.00,2,'t0002'go;with tas(select     a.buy_code,    b.item_pro,    SUM(b.item_amount) as item_amount,    sum(b.item_amount*b.item_price) as total_buyfrom     x_buy a,x_buyitem bwhere     a.buy_code=b.item_buy     and a.buy_tally=2group by    a.buy_code,    b.item_pro),m as(select     a.buyout_code,    b.item_pro,    sum(b.item_amount*b.item_price) as total_buy,    SUM(b.item_amount) as item_amountfrom     x_buyout a,x_buyoutitem bwhere     a.buyout_code=b.item_buyout     and a.buyout_tally=2group by    a.buyout_code,    b.item_pro),n as(select     t.item_pro,t.total_buy-m.total_buy/t.item_amount-m.item_amount as [pro_cost]from tinner join m on t.item_pro=m.item_pro)update     [x_pro]set     [x_pro].[pro_cost]=n.pro_costfrom     n where     n.item_pro=[x_pro].pro_id    select * from [x_pro]/*pro_id    pro_name    pro_cost1    电视    992    冰箱    17*/--把第一个表需要更新的字段的类型改一下，不然的话小数点后的数字没了
------解决方案--------------------
SQL code
SELECT a.pro_id,a.pro_name,(采购金额-退货金额)/(采购数量-退货数量)FROM (SELECT a.pro_id,a.pro_name,SUM(b.item_amount*b.item_price) AS 采购金额,SUM(b.item_amount ) AS 采购数量FROM 商品表x_pro aJOIN 采购明细表x_buyitem  b ON a.pro_id=b.item_proJOIN 采购单表x_buy c ON b.item_buy=c.buy_code AND buy_tally=2GROUP BY  a.pro_id,a.pro_name) aJOIN (SELECT a.pro_id,a.pro_name,SUM(b.item_amount*b.item_price) AS 退货金额,SUM(b.item_amount ) AS 退货数量FROM 商品表x_pro aJOIN 采购退货明细表x_buyoutitem  b ON a.pro_id=b.item_proJOIN 采购退货单表x_buyout c ON  a.item_buyout=c.buyout_code AND buyout_tally=2GROUP BY  a.pro_id,a.pro_name) b ON a.pro_id=b.pro_id