--> 测试数据：[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 all
select 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 all
select 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 all
select 2,3,10.00,1,'B0001' union all
select 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 all
select 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 all
select 2,1,10.00,1,'t0001' union all
select 3,1,12.00,2,'t0002'
go

;with t
as(
select 
    a.buy_code,
    b.item_pro,
    SUM(b.item_amount) as item_amount,
    sum(b.item_amount*b.item_price) as total_buy
from 
    x_buy a,x_buyitem b
where 
    a.buy_code=b.item_buy 
    and a.buy_tally=2
group 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_amount
from 
    x_buyout a,x_buyoutitem b
where 
    a.buyout_code=b.item_buyout 
    and a.buyout_tally=2
group 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 t
inner join m 
on t.item_pro=m.item_pro
)
update 
    [x_pro]
set 
    [x_pro].[pro_cost]=n.pro_cost
from 
    n 
where 
    n.item_pro=[x_pro].pro_id
    
select * from [x_pro]
/*
pro_id    pro_name    pro_cost
1    电视    99
2    冰箱    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 a
JOIN 采购明细表x_buyitem  b ON a.pro_id=b.item_pro
JOIN 采购单表x_buy c ON b.item_buy=c.buy_code AND buy_tally=2
GROUP BY  a.pro_id,a.pro_name
) a
JOIN