SQL Server merge 语句详解。集增、删、改一体的语句

上代码。构造测式数据

---商品表  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下来 去执行看看吧。