使用 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
uj5u.com熱心網友回復：
您可以通過多種方式執行此操作。一種是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);
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/323669.html
標籤：sql sql-server 子查询 带领