上代码。构造测式数据
---商品表 goodscode 商品编号 goodsname商品名称
declare @goods table(goodscode int,goodsname varchar(100))
--- 单据表 billid 单据id ,goodscode 商品编号 qty 数量 入库为正，出库为负
declare @bill table(billid int,goodscode int,qty int)
---库存表 goodscode 商品编号 qty现有库存
declare @goodsstock table(goodscode int,qty int)
insert into @goods
values(1,'苹果'),(2,'小米'),(3,'华为'),(4,'一加')
insert into @bill
values(1,1,10),(1,2,10),(1,3,10),(2,3,-10)
insert into @goodsstock
values(1,9),(3,10),(4,10)
数据构造完成，我们现在的库存表的数据全是错的，现在需要根据bill表中的数据来修复 @goodsstock 库存表
select a.goodscode,a.goodsname,isnull(sum(b.qty),0) as qty
from @goods a
inner join @bill b on a.goodscode = b.goodscode
group by a.goodscode,a.goodsname
goodscode goodsname qty
1 苹果 10
2 小米 10
3 华为 0
现在就根据单据统计数据来修复@goodsstock 库存表数据
当统计的最终qty = 0时，需要从@goodsstock表中删除
;with cte as
(
select a.goodscode,a.goodsname,isnull(sum(b.qty),0) as qty
from @goods a
inner join @bill b on a.goodscode = b.goodscode
group by a.goodscode,a.goodsname
)
merge @goodsstock as [target]
using cte as [source]
on [target].goodscode = [source].goodscode
when matched and [source].qty = 0 then --on 指定的关联条件 在源表中有 在目标表中也有则可以执行 update 或 delete
delete
when matched then
update set qty = [source].qty
when not matched by target then ---on 指定的关联条件 在源表中有，则目标表中没有 这里下面只能写insert
insert(goodscode,qty)
values( [source].goodscode,[source].qty)
when not matched by source then ---on 指定的关联条件 在目标表中有，但在源表中没有 这里可以写 delete 也可以写update 我这里需要执行delete
delete ;
select b.*,a.qty from @goodsstock a inner join @goods b on a.goodscode = b.goodscode
我们查看执行后的结果
goodscode goodsname qty
1 苹果 10
2 小米 10
再来看看output在 同merge语句中的应用
output子句使用 传送门 https://www.jianshu.com/p/e44e40f89868
merge @goodsstock as [target]
using cte as [source]
on [target].goodscode = [source].goodscode
when matched and [source].qty = 0 then --on 指定的关联条件 在源表中有 在目标表中也有则可以执行 update 或 delete
delete
when matched then
update set qty = [source].qty
when not matched by target then ---on 指定的关联条件 在源表中有，则目标表中没有 这里下面只能写insert
insert(goodscode,qty)
values( [source].goodscode,[source].qty)
when not matched by source then ---on 指定的关联条件 在目标表中有，但在源表中没有 这里可以写 delete 也可以写update 我这里需要执行delete
delete
output $action,Inserted.goodscode ,Inserted.qty ,deleted.goodscode,deleted.qty
;
$action 这个是merge独有的，代表操作类型 有insert,update,delete三种。 想知道最终执行的结果，把代码copy下来 去执行看看吧。
