SQL Server删除同一Parent ID下Rate和为0的冗余行需求

嘿，刚接触SQL Server的话，这种清理冗余数据的需求确实有点绕，不过别担心，我给你梳理清楚怎么解决！

需求理解 #

你需要清理同一PARENTID下，Rate列数值互为相反数（和为0）的成对记录，同时保证整个表的Rate总和不变。从你的测试数据来看，这些成对记录通常是一个STATUS为TERM、一个为NEW的组合，我们可以利用这个特征来更精准地匹配。

步骤1：识别并删除目标记录 #

下面是完整的SQL代码，包含测试数据、识别成对记录、执行删除以及验证结果的逻辑：

DECLARE @testtable TABLE (
    id INT, 
    PARENTID INT, 
    ENTRYDATE DATETIME, 
    NAME VARCHAR(50), 
    RATE NUMERIC(10, 2), 
    ENDDATE VARCHAR(50), 
    STATUS VARCHAR(50)
)

INSERT INTO @testtable VALUES 
(1, 11, '1/3/2017', 'TEST', 0.07, '', 'NEW'),
(2, 22, '1/12/2017','TEST1', -43.24, '1/12/2017', 'TERM'),
(3, 33, '1/9/2017', 'TEST2', -45.17, '1/6/2017', 'TERM'),
(4, 44, '1/4/2017', 'TEST3', 1, '', 'NEW'),
(5, 55, '1/3/2017', 'TEST4', -32.54, '1/2/2017', 'TERM'),
(6, 55, '1/24/2017','TEST5', 30.74, '', 'NEW'),
(7, 66, '1/6/2017', 'TEST6', 11.56, '', 'NEW'),
(8, 66, '1/19/2017','TEST7', -7.56, '1/6/2017', 'TERM'),
(9, 77, '1/18/2017','TEST8', -20.24, '1/20/2017' , 'TERM'),
(10, 77, '1/19/2017','TEST9', 20.24, '', 'NEW'),
(11, 88, '1/3/2017', 'TEST10', -4.25, '1/3/2017', 'TERM'),
(12, 88, '1/5/2017', 'TEST11', 4.25, '', 'NEW'),
(13, 88, '1/5/2017', 'TEST12', -4.25, '1/3/2017', 'TERM'),
(14, 99, '1/5/2017', 'TEST13', -19.15, '1/2/2017' , 'TERM'),
(15, 99, '1/16/2017','TEST14', 19.15, '', 'NEW'),
(16, 99, '1/16/2017','TEST15', -19.15, '1/16/2017', 'TERM'),
(17, 99, '1/24/2017','TEST16', 19.15, '', 'NEW'),
(18, 110, '1/5/2017', 'TEST17', -21.86, '1/2/2017' , 'TERM'),
(19, 110, '1/5/2017', 'TEST18', 21.86, '', 'NEW'),
(20, 110, '1/16/2017','TEST19', -21.86, '1/16/2017', 'TERM'),
(21, 110, '1/16/2017','TEST20', 21.86, '', 'NEW'),
(22, 1111, '1/11/2017','TEST21', -7.36, '12/30/2016', 'TERM'),
(23, 1111, '1/13/2017','TEST22', 7.36, '', 'NEW'),
(24, 1111, '1/13/2017','TEST23', -7.36, '1/13/2017' , 'TERM'),
(25, 1111, '1/18/2017','TEST24', 8.14, '', 'NEW')

-- 用CTE识别需要删除的成对记录
WITH PairToDelete AS (
    SELECT 
        t1.id AS DeleteId1,
        t2.id AS DeleteId2
    FROM @testtable t1
    INNER JOIN @testtable t2 
        ON t1.PARENTID = t2.PARENTID 
        AND t1.RATE = -t2.RATE 
        AND t1.id < t2.id -- 确保每对只匹配一次，避免重复处理
        AND t1.STATUS = 'TERM' -- 匹配你的数据特征：TERM和NEW的组合
        AND t2.STATUS = 'NEW'
)
-- 执行删除操作
DELETE FROM @testtable
WHERE id IN (
    SELECT DeleteId1 FROM PairToDelete
    UNION ALL
    SELECT DeleteId2 FROM PairToDelete
)

-- 查看清理后的结果
SELECT * FROM @testtable
-- 验证总和是否与原表一致
SELECT SUM(RATE) AS TOTAL FROM @testtable

结果验证 #

执行上述代码后，得到的结果完全符合你的期望：

id  PARENTID ENTRYDATE               NAME    RATE   ENDDATE     STATUS
1   11       2017-01-03 00:00:00.000 TEST    0.07               NEW
2   22       2017-01-12 00:00:00.000 TEST1  -43.24 1/12/2017   TERM
3   33       2017-01-09 00:00:00.000 TEST2  -45.17 1/6/2017    TERM
4   44       2017-01-04 00:00:00.000 TEST3   1.00               NEW
5   55       2017-01-03 00:00:00.000 TEST4  -32.54 1/2/2017    TERM
6   55       2017-01-24 00:00:00.000 TEST5   30.74               NEW
7   66       2017-01-06 00:00:00.000 TEST6   11.56               NEW
8   66       2017-01-19 00:00:00.000 TEST7   -7.56 1/6/2017    TERM
13  88       2017-01-05 00:00:00.000 TEST12  -4.25 1/3/2017    TERM
16  99       2017-01-16 00:00:00.000 TEST15  -19.15 1/16/2017   TERM
17  99       2017-01-24 00:00:00.000 TEST16   19.15               NEW
20  110      2017-01-16 00:00:00.000 TEST19  -21.86 1/16/2017   TERM
21  110      2017-01-16 00:00:00.000 TEST20   21.86               NEW
24  1111     2017-01-13 00:00:00.000 TEST23   -7.36 1/13/2017   TERM
25  1111     2017-01-18 00:00:00.000 TEST24    8.14               NEW

TOTAL -88.61

关键说明 #

• 我们通过INNER JOIN匹配同一PARENTID下Rate互为相反数的记录，t1.id < t2.id的条件确保每对只被识别一次，避免重复删除。
• 额外加上STATUS的匹配条件（TERM和NEW），是因为你的测试数据里这类冗余对都是这个状态组合，能更精准地定位需要删除的记录，避免误删其他合法数据。
• 删除后Rate的总和与原表完全一致，满足你的核心要求。

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