使用DELETE FROM和LEAD函数

Question

使用SQL Server 2014，我有一个跟踪收银机销售的表。如果寄存器操作符扫描错误的项，它将创建两条记录-一条SALE记录和一条VOID记录。

TRX_ID   LINE_ID   STS     ITEM_DESC
-----------------------------------------------------
123456   1         Sale    Sunglasses Mens RS124
123456   2         Void    Sunglasses Mens RS124
123456   3         Sale    Sunglasses NXR RS977
123456   4         Void    Sunglasses NXR RS977
123456   5         Sale    Sunglasses Unisex RS355
123678   1         Sale    Sunglasses Womens RS124w
123678   2         Void    Sunglasses Womens RS124w
123678   3         Sale    Sunglasses Womens RS977w
123678   4         Sale    Sunglasses Womens RS977w
123678   5         Sale    Sunglasses Unisex RS355w

我需要写一个查询来删除记录，如果事务中的以下项目已无效(STS='VOID')，则只保留'net'事务。

我使用LEAD让查询检查以下记录，我只是不知道如何将DELETE FROM和LEAD组合在一起。我尝试使用这个查询没有成功:

DELETE FROM SALES 
WHERE xxx IN (SELECT 
TRX_ID, LINE_ID, LEAD (T1.STS, 1, 0) OVER (PARTITION BY T1.TRX_ID ORDER BY T1.LINE_ID)  NEXT_STS
FROM SALES_TRANSACTIONS T1) 
WHERE NEXT_STS = 'V' 

除结果:删除以下记录:

TRX_ID   LINE_ID   STS     ITEM_DESC
----------------------------------------------------
123456   1         Sale    Sunglasses Mens RS124
123456   2         Void    Sunglasses Mens RS124
123456   3         Sale    Sunglasses NXR RS977
123456   4         Void    Sunglasses NXR RS977
123678   1         Sale    Sunglasses Womens RS124w
123678   2         Void    Sunglasses Womens RS124w

Answer 1

您可以通过几种方式做到这一点。一种是在公共表表达式中使用lead，然后从中删除:

with extra as (
select sts,
lead(sts) over (partition by trx_id order by line_id) leadsts
from   sales
)
delete 
from   extra
where  'Void' in (sts, leadsts);