满足正负金额抵消等条件时删除Billing表重复记录的SQL问题

需求明确 #

仅当两条记录同时满足以下全部条件时，判定为可抵消重复记录，两条均执行删除：

• SerialNo（流水号）相同
• Code（编码）相同
• DispenseDt（日期）相同
• 账单金额BillAmt为绝对值相等的正负值

实现思路 #

将数据按SerialNo、Code、DispenseDt、BillAmt的绝对值四个维度分组，若同一分组内的金额总和为0，说明该组存在刚好完全抵消的正负金额记录，该组所有记录都需要删除；剩余总和不为0的分组的记录即为需要保留的内容。

代码实现 #

查询保留记录 #

SELECT BillId, SerialNo, BillAmt, Code, DispenseDt
FROM (
    SELECT *,
           SUM(BillAmt) OVER (
               PARTITION BY SerialNo, Code, DispenseDt, ABS(BillAmt)
           ) AS group_total
    FROM Billing
    -- 你原有业务过滤条件可加在此处，示例：
    -- WHERE InvoiceDt BETWEEN '20200601' AND '20200630'
    --   AND FacID IN ('IND600','IND605','IND610','IND620','IND630','IND640','IND650','IND660','IND670','IND680','IND690','IND695')
) t
WHERE group_total != 0
ORDER BY BillId;

直接删除重复记录 #

如果需要直接从原表删除可抵消的重复记录，执行以下语句：

DELETE FROM Billing
WHERE BillId IN (
    SELECT BillId
    FROM (
        SELECT BillId,
               SUM(BillAmt) OVER (
                   PARTITION BY SerialNo, Code, DispenseDt, ABS(BillAmt)
               ) AS group_total
        FROM Billing
    ) t
    WHERE group_total = 0
);

效果验证 #

执行查询语句后，返回结果和你给出的期望输出完全一致：

内容的提问来源于stack exchange，提问作者plummdg