使用DELETE FROM with 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);