员工职位变动时如何插入职位表新记录，现有SQL方案是否合理

原有代码逻辑问题汇总 #

第一版代码问题 #

• NULL值判断错误：SQL中dst.enddate = NULL永远返回空，不会命中任何行，必须改用dst.enddate IS NULL
• 不等判断漏场景：src.column1 != dst.column1只要任意一侧为NULL，结果就为空，会漏掉NULL值变化的更新场景，应该用IS DISTINCT FROM替代
• INSERT语法错误：你写的INSERT语句里用了src.employee = dst.employee的关联条件，但INSERT的SELECT子句根本没有绑定dst表，语法层面就无法运行
• 重复插入风险：没有过滤临时表里和当前在职记录完全相同的行，会插入无意义的重复数据

第二版调整后代码的遗留问题 #

• 字段名笔误：dst.employeeI多打了字母I，直接语法报错
• 仍存在endDate = NULL的错误判断，需要改成IS NULL
• CASE语法错误：UPDATE语句中SET后面直接跟CASE不符合语法，正确写法是SET enddate = CASE WHEN ... END
• 全局修改临时表startdate风险：直接把所有临时表的startdate改成当前日期，会覆盖临时表自带的生效时间，如果你需要保留原始生效时间的话这个操作完全错误
• 时间区间重叠风险：如果旧记录startdate和新记录startdate相同，把旧记录enddate设为新startdate会导致新老记录时间点重叠，标准的慢变维度SCD2设计里应该把旧记录enddate设为src.startdate - 1天保证区间不重叠

更优实现方案 #

以下是适配你场景的标准SCD2（缓慢变化维度类型2）实现逻辑：

-- 第一步：清理临时表里和当前在职记录完全一致的无效数据（无字段变更不需要更新）
DELETE FROM tmp t
WHERE EXISTS (
    SELECT 1 FROM saireco.position p
    WHERE p.employee = t.employee
      AND p.enddate IS NULL -- 只匹配当前在职的记录
      AND t.column1 IS NOT DISTINCT FROM p.column1
      AND t.column2 IS NOT DISTINCT FROM p.column2
      -- 其他需要对比的字段都按上面的格式补充即可
);

-- 第二步：更新老在职记录的失效时间
UPDATE saireco.position dst
SET enddate = src.startdate - INTERVAL '1 day' -- 如果你的时间区间是左闭右开设计，直接用src.startdate即可
FROM tmp src
WHERE dst.employee = src.employee
  AND dst.enddate IS NULL; -- 只更新当前在职的记录

-- 第三步：插入新的职位记录
INSERT INTO saireco.position (employee, startdate, enddate, column1, column2, 其余字段按实际补充)
SELECT employee, startdate, NULL AS enddate, column1, column2, 其余对应字段
FROM tmp;

如果你用的是MySQL这类不支持IS NOT DISTINCT FROM的数据库，可以替换成NOT (t.column1 <=> p.column1)的写法，效果完全一致。

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