如何在Postgres中删除未关联employee_details的Employee表重复行

前置逻辑说明 #

• 重复记录判定规则：Name + Surname 两个字段值完全一致的员工记录视为重复
• 保留优先级：同组重复记录中，已在employee_details表存在关联映射的优先保留，其余无关联的重复记录删除

步骤1：先查询核验待删除记录（避免误操作） #

执行以下SQL确认待删除的记录是否符合预期：

SELECT e.id, e.name, e.surname
FROM employee e
WHERE 
  -- 筛选未关联employee_details表的记录
  NOT EXISTS (
    SELECT 1 FROM employee_details ed WHERE ed.employee_id = e.id
  )
  -- 筛选同姓名组下存在已关联子表的记录（即当前记录为冗余重复项）
  AND EXISTS (
    SELECT 1 FROM employee e2
    JOIN employee_details ed2 ON ed2.employee_id = e2.id
    WHERE e2.name = e.name AND e2.surname = e.surname
  );

你的示例数据执行后会返回ID为143、445的两条记录，符合删除要求。

步骤2：执行删除操作 #

确认核验结果无误后，执行以下删除SQL：

DELETE FROM employee e
WHERE 
  NOT EXISTS (
    SELECT 1 FROM employee_details ed WHERE ed.employee_id = e.id
  )
  AND EXISTS (
    SELECT 1 FROM employee e2
    JOIN employee_details ed2 ON ed2.employee_id = e2.id
    WHERE e2.name = e.name AND e2.surname = e.surname
  );

通用扩展方案（窗口函数版） #

如果需要适配更复杂的保留规则（比如同组无关联记录时保留ID最大的记录），可以使用窗口函数实现：

WITH employee_rank AS (
  SELECT 
    id,
    ROW_NUMBER() OVER(
      PARTITION BY name, surname 
      -- 排序规则：已关联子表的排最前，其余按ID倒序排列
      ORDER BY CASE WHEN EXISTS (SELECT 1 FROM employee_details ed WHERE ed.employee_id = employee.id) THEN 0 ELSE 1 END, id DESC
    ) AS rank_num
  FROM employee
)
DELETE FROM employee WHERE id IN (SELECT id FROM employee_rank WHERE rank_num > 1);

该方案会自动保留每个姓名组内排序第一的记录，其余重复项全部删除。

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