SQL Server中如何结合DELETE FROM与LEAD函数删除配对销售作废记录

问题场景 #

我使用的是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'），则删除这两条关联记录，仅保留最终有效的净交易记录。

错误尝试 #

我计划用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

解决方案 #

SQL Server支持直接对公用表表达式（CTE）执行删除操作，你可以先通过CTE计算出每行的上下行关联数据，再直接匹配删除即可：

WITH SalesWithRelation AS (
    SELECT 
        *,
        -- 取同交易下一行的状态
        LEAD(STS) OVER (PARTITION BY TRX_ID ORDER BY LINE_ID) AS NextSTS,
        -- 取同交易下一行的商品描述，用于配对校验
        LEAD(ITEM_DESC) OVER (PARTITION BY TRX_ID ORDER BY LINE_ID) AS NextItem,
        -- 取同交易上一行的状态
        LAG(STS) OVER (PARTITION BY TRX_ID ORDER BY LINE_ID) AS PrevSTS,
        -- 取同交易上一行的商品描述，用于配对校验
        LAG(ITEM_DESC) OVER (PARTITION BY TRX_ID ORDER BY LINE_ID) AS PrevItem
    FROM SALES_TRANSACTIONS
)
DELETE FROM SalesWithRelation
WHERE 
    -- 匹配需要删除的Sale记录：当前是Sale，下一行是同商品的Void
    (STS = 'Sale' AND NextSTS = 'Void' AND ITEM_DESC = NextItem)
    -- 匹配需要删除的Void记录：当前是Void，上一行是同商品的Sale
    OR (STS = 'Void' AND PrevSTS = 'Sale' AND ITEM_DESC = PrevItem);

注意事项

执行删除前可以把DELETE FROM SalesWithRelation替换为SELECT * FROM SalesWithRelation，确认查询出来的待删除记录和你的预期完全一致后再执行删除操作，避免误删数据。如果你的业务逻辑可以保证同交易下连续的Sale+Void一定是配对的错扫记录，也可以去掉商品描述匹配的判断条件。

内容的提问来源于stack exchange，提问作者Depth of Field