SQL Server：筛选分组后Amount求和非零的行并保留原表结构

场景说明 #

你需要按DebtorID分组，移除分组内Amount列中可相互抵消（和为零）的行，保留剩余求和非零的行，并将结果存入与原表#myData结构一致的新表。以下是两种实用解决方案，适配不同业务需求：

测试数据准备 #

先创建测试表和模拟数据，方便验证效果：

CREATE TABLE #myData (
    ID INT,
    DebtorID INT,
    Amount DECIMAL(18,2),
    Notes VARCHAR(50)
);

INSERT INTO #myData VALUES
(1, 1, 100.00, 'Invoice 1'),
(2, 1, -100.00, 'Payment 1'),
(3, 1, 50.00, 'Invoice 2'),
(4, 2, 200.00, 'Invoice 3'),
(5, 2, -50.00, 'Payment 2'),
(6, 2, -50.00, 'Payment 3'),
(7, 3, 150.00, 'Invoice 4'),
(8, 3, -70.00, 'Payment 4'),
(9, 3, -60.00, 'Payment 5'),
(10, 4, -30.00, 'Refund 1'),
(11, 4, 30.00, 'Charge 1');

方法1：移除完全配对的抵消行（金额相等、正负相反） #

适用于仅需要移除金额完全相等、正负相反的配对行，剩余行保留原始金额。此方法高效，适合不需要拆分单行的场景：

WITH DebtorTotals AS (
    -- 筛选总金额非零的DebtorID
    SELECT DebtorID, SUM(Amount) AS TotalAmount
    FROM #myData
    GROUP BY DebtorID
),
PairedRows AS (
    -- 给同DebtorID下的同金额正负行分配配对编号
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY DebtorID, ABS(Amount), SIGN(Amount) ORDER BY ID) AS PairNum
    FROM #myData
)
-- 插入结果到新表，保留未被配对的行，排除总金额为零的分组
SELECT m.* INTO #myData_Cleaned
FROM #myData m
JOIN DebtorTotals dt ON m.DebtorID = dt.DebtorID
WHERE dt.TotalAmount <> 0
AND NOT EXISTS (
    -- 排除存在配对抵消的行
    SELECT 1
    FROM PairedRows pr1
    JOIN PairedRows pr2 
        ON pr1.DebtorID = pr2.DebtorID
        AND pr1.PairNum = pr2.PairNum
        AND pr1.Amount = -pr2.Amount
        AND pr1.ID <> pr2.ID
    WHERE pr1.ID = m.ID
);

执行结果：

• DebtorID=1：移除100和-100的行，保留50的行
• DebtorID=2：保留所有行（两个-50无法完全抵消200）
• DebtorID=3：保留所有行
• DebtorID=4：总金额为零，全部移除

方法2：完全抵消所有可抵消金额（支持部分抵消） #

如果需要尽可能抵消所有正负金额，仅保留最终无法抵消的部分（可能需要拆分单行金额），适合需要彻底抵消的场景：

WITH DebtorTotals AS (
    -- 筛选总金额非零的DebtorID
    SELECT DebtorID, SUM(Amount) AS TotalAmount
    FROM #myData
    GROUP BY DebtorID
    HAVING SUM(Amount) <> 0
),
PositiveCumulative AS (
    -- 正金额行按金额降序计算累计和
    SELECT 
        ID,
        DebtorID,
        Amount,
        Notes,
        SUM(Amount) OVER (PARTITION BY DebtorID ORDER BY Amount DESC, ID) AS CumSum
    FROM #myData
    WHERE Amount > 0
        AND DebtorID IN (SELECT DebtorID FROM DebtorTotals)
),
NegativeCumulative AS (
    -- 负金额行按金额升序计算累计和
    SELECT 
        ID,
        DebtorID,
        Amount,
        Notes,
        SUM(Amount) OVER (PARTITION BY DebtorID ORDER BY Amount ASC, ID) AS CumSum
    FROM #myData
    WHERE Amount < 0
        AND DebtorID IN (SELECT DebtorID FROM DebtorTotals)
),
FinalPositive AS (
    -- 确定总金额为正的分组中需要保留的正行（含部分抵消后的剩余金额）
    SELECT 
        ID,
        DebtorID,
        CASE 
            WHEN CumSum <= TotalAmount THEN Amount
            ELSE Amount - (CumSum - TotalAmount)
        END AS Amount,
        Notes
    FROM PositiveCumulative pc
    JOIN DebtorTotals dt ON pc.DebtorID = dt.DebtorID
    WHERE dt.TotalAmount > 0
        AND (CumSum - Amount) < TotalAmount
),
FinalNegative AS (
    -- 确定总金额为负的分组中需要保留的负行（含部分抵消后的剩余金额）
    SELECT 
        ID,
        DebtorID,
        CASE 
            WHEN CumSum >= TotalAmount THEN Amount
            ELSE Amount - (CumSum - TotalAmount)
        END AS Amount,
        Notes
    FROM NegativeCumulative nc
    JOIN DebtorTotals dt ON nc.DebtorID = dt.DebtorID
    WHERE dt.TotalAmount < 0
        AND (CumSum - Amount) > TotalAmount
)
-- 合并结果并插入新表
SELECT * INTO #myData_Cleaned
FROM FinalPositive
UNION ALL
SELECT * FROM FinalNegative
ORDER BY DebtorID, ID;

执行结果：

• DebtorID=1：保留50.00的行
• DebtorID=2：保留200.00的行，金额调整为100.00（抵消两个-50的总和）
• DebtorID=3：保留150.00的行，金额调整为20.00（抵消-70和-60的总和）
• DebtorID=4：全部移除

注意事项 #

• 新表#myData_Cleaned的结构与原表#myData完全一致，可直接用于后续业务。
• 可根据实际业务调整窗口函数中的ORDER BY规则（比如按交易日期排序，优先抵消最早的行）。
• 若数据量较大，方法1的性能优于方法2，建议优先选择方法1（除非需要部分抵消的逻辑）。

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