如何基于RENEGOCIACAO列删除最早重复行或修改重复值？

针对SANKHYA库中TGFFIN和TCSCON表的重复问题，核心思路是用窗口函数ROW_NUMBER()按RENEGOCIACAO分组，标记出每组中最早的记录，再执行删除或更新操作。

一、删除RENEGOCIACAO重复组中最早的整行记录 #

通过窗口函数定位每个重复组内的最早行（按创建时间DTCRIACAO升序排序，若有唯一主键也可替代），然后删除这些行：

CTE写法（支持多数现代SQL引擎） #

WITH DuplicateRecords AS (
    SELECT 
        tg.CODFIN,
        ROW_NUMBER() OVER (
            PARTITION BY tg.RENEGOCIACAO 
            ORDER BY tg.DTCRIACAO ASC
        ) AS row_rank
    FROM TGFFIN tg
    JOIN TCSCON tc ON tg.CODCON = tc.CODCON
    WHERE tg.RENEGOCIACAO IN (60448, 66016) -- 可去掉此条件处理所有重复值
)
DELETE FROM TGFFIN
WHERE CODFIN IN (SELECT CODFIN FROM DuplicateRecords WHERE row_rank = 1);

兼容子查询写法（若不支持CTE删除） #

DELETE FROM TGFFIN
WHERE CODFIN IN (
    SELECT CODFIN
    FROM (
        SELECT 
            CODFIN,
            ROW_NUMBER() OVER (
                PARTITION BY RENEGOCIACAO 
                ORDER BY DTCRIACAO ASC
            ) AS row_rank
        FROM TGFFIN
        WHERE RENEGOCIACAO IN (60448, 66016)
    ) sub_query
    WHERE row_rank = 1
);

二、无法删除时，将最早的重复值改为NULL #

若权限或业务限制无法删除行，可将对应行的RENEGOCIACAO字段更新为NULL：

CTE更新写法 #

WITH DuplicateRecords AS (
    SELECT 
        tg.CODFIN,
        ROW_NUMBER() OVER (
            PARTITION BY tg.RENEGOCIACAO 
            ORDER BY tg.DTCRIACAO ASC
        ) AS row_rank
    FROM TGFFIN tg
    JOIN TCSCON tc ON tg.CODCON = tc.CODCON
    WHERE tg.RENEGOCIACAO IN (60448, 66016)
)
UPDATE TGFFIN
SET RENEGOCIACAO = NULL
WHERE CODFIN IN (SELECT CODFIN FROM DuplicateRecords WHERE row_rank = 1);

兼容子查询更新写法 #

UPDATE TGFFIN
SET RENEGOCIACAO = NULL
WHERE CODFIN IN (
    SELECT CODFIN
    FROM (
        SELECT 
            CODFIN,
            ROW_NUMBER() OVER (
                PARTITION BY RENEGOCIACAO 
                ORDER BY DTCRIACAO ASC
            ) AS row_rank
        FROM TGFFIN
        WHERE RENEGOCIACAO IN (60448, 66016)
    ) sub_query
    WHERE row_rank = 1
);

注意：

• 替换DTCRIACAO为实际用于判断“最早”的字段（如主键CODFIN升序，若创建时间不可靠）
• 执行前建议先通过SELECT语句验证要删除/更新的记录，避免误操作

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