如何将MAIN表中ID重复的非空TERMDATE记录迁移至重复表

首先明确需求核心：保留MAIN表中所有TERMDATE为NULL的记录，把那些和这些NULL记录ID重复且TERMDATE不为NULL的记录，迁移到单独的重复表中。下面分步骤来实现，我会尽量写通用的SQL逻辑，同时标注不同数据库的细节差异。

1. 先创建重复表（用于存放迁移的记录） #

首先得创建一个和MAIN表结构完全一致的表，用来存要移走的重复记录：

• MySQL 写法：

CREATE TABLE DUPLICATE_TABLE LIKE MAIN;

• Oracle/SQL Server 写法（复制表结构不复制数据）：

CREATE TABLE DUPLICATE_TABLE AS SELECT * FROM MAIN WHERE 1=0;

2. 将符合条件的记录迁移到重复表 #

我们需要筛选出：ID存在重复、该ID下有TERMDATE为NULL的记录、且当前记录的TERMDATE不为NULL的行，把它们插入到重复表。这里用窗口函数来实现会很清晰：

INSERT INTO DUPLICATE_TABLE
SELECT *
FROM (
    SELECT 
        *,
        -- 统计每个ID的总记录数（判断是否重复）
        COUNT(*) OVER (PARTITION BY ID) AS id_total,
        -- 标记该ID下是否存在TERMDATE为NULL的记录
        MAX(CASE WHEN TERMDATE IS NULL THEN 1 ELSE 0 END) OVER (PARTITION BY ID) AS has_null_term
    FROM MAIN
) temp
WHERE id_total > 1          -- ID有重复
  AND has_null_term = 1     -- 该ID下存在要保留的NULL记录
  AND TERMDATE IS NOT NULL; -- 这条记录是需要迁移的非NULL记录

逻辑说明 #

对应你的示例数据：

• ID A021有两条记录，其中一条TERMDATE为NULL，另一条非NULL → 非NULL的那条会被选中插入重复表
• ID X039两条都是TERMDATE为NULL → 不会被选中，全部保留在MAIN表

3. 从MAIN表中删除已迁移的记录 #

重复表存好数据后，我们需要把MAIN表中这些已迁移的记录删掉。可以用关联删除确保精准操作：

MySQL/SQL Server 写法 #

DELETE m
FROM MAIN m
JOIN DUPLICATE_TABLE d 
  ON m.ID = d.ID 
  AND m.SYSTEM = d.SYSTEM 
  AND m.FIRST_NAME = d.FIRST_NAME 
  AND m.LAST_NAME = d.LAST_NAME 
  AND m.TERMDATE = d.TERMDATE;

Oracle 写法 #

DELETE FROM MAIN m
WHERE EXISTS (
    SELECT 1 FROM DUPLICATE_TABLE d
    WHERE m.ID = d.ID 
      AND m.SYSTEM = d.SYSTEM 
      AND m.FIRST_NAME = d.FIRST_NAME 
      AND m.LAST_NAME = d.LAST_NAME 
      AND m.TERMDATE = d.TERMDATE
);

验证结果 #

执行完上面的步骤后，你可以查询两张表确认：

• MAIN表应该只保留所有TERMDATE为NULL的记录（包括像X039那样的重复NULL记录）
• DUPLICATE_TABLE里应该只有那些和NULL记录ID重复的非NULL记录

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