SQL插入app_eduBg表时避免schoolID+appID组合重复的实现咨询

需求实现方案 #

核心要保证插入的(schoolID, appID)组合不重复，可按以下两种方案调整：

方案1：插入前对源数据去重（兼容所有数据库，无需改表结构） #

直接在查询阶段过滤掉重复的(schoolID, appID)组合，你的场景中schoolID固定为1，所以只要保证同一个applicantID只取一条即可，调整后代码如下：

insert into app_eduBg
(
    schoolID,
    appID,
    studentID,
    stateID,
    countryID,
    age,
    startYear,
    startMonth,
    endYear,
    endMonth,
    finalGrade
)
SELECT 
    1,
    applicantID,
    0,
    0,
    0,
    coalesce(latestFormerSchAge,Null),
    coalesce(latestFormerSchFromYear,Null),
    coalesce(latestFormerSchFromMonth,Null),
    coalesce(latestFormerSchToYear,Null),
    coalesce(latestFormerSchToMonth,Null),
    11
FROM (
    SELECT *,
        -- 按schoolID+appID分组去重，同一个组合只取第一条
        ROW_NUMBER() OVER(PARTITION BY 1, applicantID ORDER BY (SELECT 1)) AS rn
    from [app_AMaster(adv)] 
    where Leaving !='Leaving'
) t
WHERE rn = 1;

如果同一个appID的多条源数据需要优先取指定规则的记录（比如时间最新的），可以把ORDER BY (SELECT 1)替换成对应的排序字段，比如ORDER BY 数据更新时间 DESC。

方案2：加唯一约束+冲突处理（更稳妥，避免后续其他写入逻辑产生重复） #

第一步：先给app_eduBg表加(schoolID, appID)组合唯一约束

ALTER TABLE app_eduBg ADD CONSTRAINT uk_school_app UNIQUE (schoolID, appID);

第二步：插入时加冲突忽略逻辑，不同数据库写法略有差异

MySQL版本

insert ignore into app_eduBg
(
    schoolID,
    appID,
    studentID,
    stateID,
    countryID,
    age,
    startYear,
    startMonth,
    endYear,
    endMonth,
    finalGrade
)
SELECT 
    1,
    applicantID,
    0,
    0,
    0,
    coalesce(latestFormerSchAge,Null),
    coalesce(latestFormerSchFromYear,Null),
    coalesce(latestFormerSchFromMonth,Null),
    coalesce(latestFormerSchToYear,Null),
    coalesce(latestFormerSchToMonth,Null),
    11
from [app_AMaster(adv)] 
where Leaving !='Leaving';

PostgreSQL版本

insert into app_eduBg
(
    schoolID,
    appID,
    studentID,
    stateID,
    countryID,
    age,
    startYear,
    startMonth,
    endYear,
    endMonth,
    finalGrade
)
SELECT 
    1,
    applicantID,
    0,
    0,
    0,
    coalesce(latestFormerSchAge,Null),
    coalesce(latestFormerSchFromYear,Null),
    coalesce(latestFormerSchFromMonth,Null),
    coalesce(latestFormerSchToYear,Null),
    coalesce(latestFormerSchToMonth,Null),
    11
from [app_AMaster(adv)] 
where Leaving !='Leaving'
ON CONFLICT (schoolID, appID) DO NOTHING;

SQL Server版本

MERGE app_eduBg AS target
USING (
    SELECT 
        1 AS schoolID,
        applicantID AS appID,
        0 AS studentID,
        0 AS stateID,
        0 AS countryID,
        coalesce(latestFormerSchAge,Null) AS age,
        coalesce(latestFormerSchFromYear,Null) AS startYear,
        coalesce(latestFormerSchFromMonth,Null) AS startMonth,
        coalesce(latestFormerSchToYear,Null) AS endYear,
        coalesce(latestFormerSchToMonth,Null) AS endMonth,
        11 AS finalGrade
    from [app_AMaster(adv)] 
    where Leaving !='Leaving'
) AS source
ON target.schoolID = source.schoolID AND target.appID = source.appID
WHEN NOT MATCHED THEN
    INSERT (schoolID, appID, studentID, stateID, countryID, age, startYear, startMonth, endYear, endMonth, finalGrade)
    VALUES (source.schoolID, source.appID, source.studentID, source.stateID, source.countryID, source.age, source.startYear, source.startMonth, source.endYear, source.endMonth, source.finalGrade);

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