SQL中聚合处理重复行、删除重复项及修复唯一键的方法

一、合并重复行的时间字段 #

先通过分组聚合得到每组重复行的最小created_datetime和最大last_seen_datetime，再批量更新原表对应字段。以下示例支持PostgreSQL、SQL Server、MySQL 8.0+：

WITH duplicate_groups AS (
    SELECT
        -- 用COALESCE将NULL转为统一标识，确保分组时NULL被视为相同值
        COALESCE(A, 'UNKNOWN') AS grp_A,
        COALESCE(B, 'UNKNOWN') AS grp_B,
        COALESCE(C, 'UNKNOWN') AS grp_C,
        COALESCE(D, 'UNKNOWN') AS grp_D,
        COALESCE(E, 'UNKNOWN') AS grp_E,
        MIN(created_datetime) AS min_created,
        MAX(last_seen_datetime) AS max_last_seen
    FROM your_table
    GROUP BY grp_A, grp_B, grp_C, grp_D, grp_E
    HAVING COUNT(*) > 1 -- 筛选出存在重复的组
)
UPDATE your_table t
JOIN duplicate_groups dg ON
    COALESCE(t.A, 'UNKNOWN') = dg.grp_A
    AND COALESCE(t.B, 'UNKNOWN') = dg.grp_B
    AND COALESCE(t.C, 'UNKNOWN') = dg.grp_C
    AND COALESCE(t.D, 'UNKNOWN') = dg.grp_D
    AND COALESCE(t.E, 'UNKNOWN') = dg.grp_E
SET
    t.created_datetime = dg.min_created,
    t.last_seen_datetime = dg.max_last_seen;

二、删除重复行 #

给每组重复行编号，保留编号为1的行（可根据需求调整排序规则），删除其余重复行：

WITH ranked_rows AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY COALESCE(A, 'UNKNOWN'), COALESCE(B, 'UNKNOWN'), COALESCE(C, 'UNKNOWN'), COALESCE(D, 'UNKNOWN'), COALESCE(E, 'UNKNOWN')
            ORDER BY created_datetime -- 按创建时间排序，保留最早的行
        ) AS row_num
    FROM your_table
)
DELETE FROM ranked_rows WHERE row_num > 1;

如果是MySQL 5.x版本（不支持CTE），可以用自连接删除：

DELETE t1 FROM your_table t1
JOIN your_table t2 ON
    COALESCE(t1.A, 'UNKNOWN') = COALESCE(t2.A, 'UNKNOWN')
    AND COALESCE(t1.B, 'UNKNOWN') = COALESCE(t2.B, 'UNKNOWN')
    AND COALESCE(t1.C, 'UNKNOWN') = COALESCE(t2.C, 'UNKNOWN')
    AND COALESCE(t1.D, 'UNKNOWN') = COALESCE(t2.D, 'UNKNOWN')
    AND COALESCE(t1.E, 'UNKNOWN') = COALESCE(t2.E, 'UNKNOWN')
    AND t1.created_datetime > t2.created_datetime; -- 删除创建时间较晚的重复行

三、修复唯一键约束 #

1. 删除现有无效的唯一键 #

根据数据库类型选择对应语句：

-- PostgreSQL/SQL Server
ALTER TABLE your_table DROP CONSTRAINT your_unique_key_name;

-- MySQL
ALTER TABLE your_table DROP INDEX your_unique_key_name;

2. 替换现有列的NULL值 #

选择符合业务逻辑的非空替代值（比如'UNKNOWN'、空字符串，避免与现有数据冲突）：

UPDATE your_table
SET
    A = COALESCE(A, 'UNKNOWN'),
    B = COALESCE(B, 'UNKNOWN'),
    C = COALESCE(C, 'UNKNOWN'),
    D = COALESCE(D, 'UNKNOWN'),
    E = COALESCE(E, 'UNKNOWN');

3. 修改列为非空约束 #

注意要指定列的原数据类型（示例用VARCHAR(255)，需替换为实际类型）：

-- PostgreSQL/SQL Server
ALTER TABLE your_table
ALTER COLUMN A SET NOT NULL,
ALTER COLUMN B SET NOT NULL,
ALTER COLUMN C SET NOT NULL,
ALTER COLUMN D SET NOT NULL,
ALTER COLUMN E SET NOT NULL;

-- MySQL
ALTER TABLE your_table
MODIFY COLUMN A VARCHAR(255) NOT NULL,
MODIFY COLUMN B VARCHAR(255) NOT NULL,
MODIFY COLUMN C VARCHAR(255) NOT NULL,
MODIFY COLUMN D VARCHAR(255) NOT NULL,
MODIFY COLUMN E VARCHAR(255) NOT NULL;

4. 重建唯一键约束 #

ALTER TABLE your_table
ADD CONSTRAINT unique_key_A_B_C_D_E UNIQUE (A, B, C, D, E);

注意事项 #

• 操作前务必备份全表数据，避免数据丢失；
• 替代值需结合业务场景选择，确保不会引发新的业务逻辑问题；
• 所有操作建议先在测试环境验证，再应用到生产环境；
• 不同数据库语法存在差异，需根据实际使用的数据库（MySQL、PostgreSQL等）调整语句。

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