如何用SQL识别并删除数据库中已冲销的发票及对应记录？

核心思路 #

要精准配对发票与冲销记录，需在accountcode和金额绝对值匹配的基础上，给同组记录添加唯一配对标识；同时通过索引优化避免全表扫描导致的性能问题。

步骤1：添加复合索引优化性能 #

先给accountcode和amount字段创建复合索引，大幅提升关联查询速度：

CREATE INDEX idx_invoice_account_amt ON your_table(accountcode, amount);

（将your_table替换为你的实际表名）

步骤2：精准定位冲销配对记录 #

使用CTE结合ROW_NUMBER()函数，给同一accountcode、同一金额绝对值的正负记录分配相同的配对编号，确保精准匹配：

WITH invoice_pairs AS (
    SELECT 
        id, -- 替换为你的表主键字段
        accountcode,
        amount,
        ROW_NUMBER() OVER (PARTITION BY accountcode, ABS(amount) ORDER BY id) AS pair_num
    FROM your_table
    WHERE amount <> 0 -- 排除无效的0金额记录
)
SELECT 
    p1.id AS original_invoice_id,
    p2.id AS reversal_invoice_id,
    p1.accountcode,
    p1.amount AS original_amount,
    p2.amount AS reversal_amount
FROM invoice_pairs p1
JOIN invoice_pairs p2 
    ON p1.accountcode = p2.accountcode
    AND ABS(p1.amount) = ABS(p2.amount)
    AND p1.amount > 0
    AND p2.amount < 0
    AND p1.pair_num = p2.pair_num;

针对你的示例数据，可添加过滤条件验证：

WITH invoice_pairs AS (
    SELECT 
        id,
        accountcode,
        amount,
        ROW_NUMBER() OVER (PARTITION BY accountcode, ABS(amount) ORDER BY id) AS pair_num
    FROM your_table
    WHERE accountcode = 'L0853' AND ABS(amount) = 242.15
)
SELECT * FROM invoice_pairs;

执行后应返回一条正金额、一条负金额的记录，且pair_num均为1，说明配对正确。

步骤3：删除冲销配对记录 #

确认配对结果无误后，执行删除操作。以下是通用的删除方式（适用于多数SQL数据库）：

WITH invoice_pairs AS (
    SELECT 
        id,
        accountcode,
        amount,
        ROW_NUMBER() OVER (PARTITION BY accountcode, ABS(amount) ORDER BY id) AS pair_num
    FROM your_table
    WHERE amount <> 0
)
DELETE FROM your_table
WHERE id IN (
    -- 收集所有需要删除的正、负记录ID
    SELECT p1.id FROM invoice_pairs p1
    JOIN invoice_pairs p2 
        ON p1.accountcode = p2.accountcode
        AND ABS(p1.amount) = ABS(p2.amount)
        AND p1.amount > 0
        AND p2.amount < 0
        AND p1.pair_num = p2.pair_num
    UNION ALL
    SELECT p2.id FROM invoice_pairs p1
    JOIN invoice_pairs p2 
        ON p1.accountcode = p2.accountcode
        AND ABS(p1.amount) = ABS(p2.amount)
        AND p1.amount > 0
        AND p2.amount < 0
        AND p1.pair_num = p2.pair_num
);

如果是MySQL数据库，可使用更高效的DELETE JOIN语法：

DELETE t1, t2
FROM your_table t1
JOIN your_table t2 
    ON t1.accountcode = t2.accountcode
    AND ABS(t1.amount) = ABS(t2.amount)
    AND t1.amount > 0
    AND t2.amount < 0
    AND t1.id < t2.id; -- 避免重复匹配删除

重要注意事项 #

• 删除前务必备份数据，或先运行SELECT语句确认要删除的记录完全符合预期，避免误删。
• 如果你的表中有其他可辅助配对的字段（如交易日期），可将其加入PARTITION BY或ORDER BY子句，进一步提升配对精准度。

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