SQL如何删除序列号重复且账单金额合计可抵消的数据行

实现方案 #

核心逻辑

对相同序列号（Serial）下的记录，匹配金额绝对值相等、符号相反、日期与Code完全一致的成对记录，这些可完全抵消的成对记录就是要删除的内容，剩余的记录即为最终保留结果。

适用支持窗口函数的数据库（MySQL 8.0+/PostgreSQL/SQL Server等）

查询符合要求的结果（先验证再操作）

WITH processed_data AS (
    -- 预处理：把带$的金额转成可计算的数值类型，给同匹配条件的正负金额编序号
    SELECT 
        Serial,
        BillAmt AS original_BillAmt,
        CAST(REPLACE(BillAmt, '$', '') AS DECIMAL(10,2)) AS BillAmt_num,
        Date,
        Code,
        ROW_NUMBER() OVER (
            PARTITION BY Serial, ABS(CAST(REPLACE(BillAmt, '$', '') AS DECIMAL(10,2))), Date, Code, SIGN(CAST(REPLACE(BillAmt, '$', '') AS DECIMAL(10,2)))
            ORDER BY Date
        ) AS rn
    FROM TableA
),
matched_pairs AS (
    -- 筛选出刚好可以正负配对的记录
    SELECT 
        Serial,
        ABS(BillAmt_num) AS abs_amt,
        Date,
        Code,
        rn
    FROM processed_data
    GROUP BY Serial, abs_amt, Date, Code, rn
    HAVING COUNT(*) = 2
)
-- 排除配对抵消的记录，输出最终结果
SELECT 
    p.Serial,
    p.original_BillAmt AS BillAmt,
    p.Date,
    p.Code
FROM processed_data p
LEFT JOIN matched_pairs m 
    ON p.Serial = m.Serial
    AND ABS(p.BillAmt_num) = m.abs_amt
    AND p.Date = m.Date
    AND p.Code = m.Code
    AND p.rn = m.rn
WHERE m.Serial IS NULL
ORDER BY p.Serial;

直接删除可抵消记录的语句

DELETE t FROM TableA t
JOIN (
    WITH processed_data AS (
        SELECT 
            Serial,
            BillAmt,
            CAST(REPLACE(BillAmt, '$', '') AS DECIMAL(10,2)) AS BillAmt_num,
            Date,
            Code,
            ROW_NUMBER() OVER (
                PARTITION BY Serial, ABS(CAST(REPLACE(BillAmt, '$', '') AS DECIMAL(10,2))), Date, Code, SIGN(CAST(REPLACE(BillAmt, '$', '') AS DECIMAL(10,2)))
                ORDER BY Date
            ) AS rn
        FROM TableA
    )
    SELECT Serial, BillAmt, Date, Code
    FROM processed_data
    WHERE (Serial, ABS(BillAmt_num), Date, Code, rn) IN (
        SELECT Serial, ABS(BillAmt_num) AS abs_amt, Date, Code, rn
        FROM processed_data
        GROUP BY Serial, abs_amt, Date, Code, rn
        HAVING COUNT(*) = 2
    )
) del_list 
ON t.Serial = del_list.Serial
AND t.BillAmt = del_list.BillAmt
AND t.Date = del_list.Date
AND t.Code = del_list.Code;

注意事项

• 若业务不需要匹配Code或者日期即可抵消，可删除PARTITION BY中对应的Code/Date字段。
• 若BillAmt本身是不带$符号的数值类型，可去掉所有REPLACE和类型转换逻辑，直接用BillAmt字段计算即可。
• 执行删除前务必先运行查询语句验证结果符合预期，避免误删数据。

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