如何编写SQL MERGE语句生成员工历史表

首先确保EMPLOYEE_HISTORY表结构正确，它需要包含原表的所有字段，加上生效和失效日期：

CREATE TABLE EMPLOYEE_HISTORY (
    EmployeeID INT,
    Name VARCHAR(100),
    Position VARCHAR(100),
    Location VARCHAR(100),
    Rate DECIMAL(10,2),
    Start_Date DATE,
    End_Date DATE,
    PRIMARY KEY (EmployeeID, Start_Date)
);

你需要分两步执行：先标记旧记录为失效，再插入最新的有效记录。以下是适配大多数关系型数据库的SQL代码：

1. 更新失效的历史记录 #

这条语句会把历史表中**当前有效（End_Date为NULL）**且数据已变更，或者员工已从最新EMPLOYEE表中移除的记录，标记为今日失效：

UPDATE EMPLOYEE_HISTORY h
SET h.End_Date = CURRENT_DATE
WHERE h.End_Date IS NULL
AND (
    -- 员工信息已变更
    EXISTS (
        SELECT 1 FROM EMPLOYEE e
        WHERE e.EmployeeID = h.EmployeeID
        AND (e.Name != h.Name OR e.Position != h.Position OR e.Location != h.Location OR e.Rate != h.Rate)
    )
    -- 员工已离职（不在最新表中）
    OR NOT EXISTS (
        SELECT 1 FROM EMPLOYEE e
        WHERE e.EmployeeID = h.EmployeeID
    )
);

2. 插入最新的有效记录 #

这条语句会把最新EMPLOYEE表中的记录插入到历史表，但只插入那些没有当前有效记录的员工（新员工或刚被标记为失效的员工）：

INSERT INTO EMPLOYEE_HISTORY (EmployeeID, Name, Position, Location, Rate, Start_Date, End_Date)
SELECT 
    e.EmployeeID,
    e.Name,
    e.Position,
    e.Location,
    e.Rate,
    CURRENT_DATE AS Start_Date,
    NULL AS End_Date
FROM EMPLOYEE e
WHERE NOT EXISTS (
    SELECT 1 FROM EMPLOYEE_HISTORY h
    WHERE h.EmployeeID = e.EmployeeID
    AND h.End_Date IS NULL
);

注意事项 #

• 日期函数需根据数据库调整：
  • SQL Server：用CAST(GETDATE() AS DATE)替代CURRENT_DATE
  • MySQL：直接用CURDATE()
  • Oracle：用TRUNC(SYSDATE)
• 执行顺序必须是先更新再插入，否则会重复插入有效记录
• 可以把这两条语句封装成存储过程，每日定时执行

示例验证 #

• Day1执行后：EMPLOYEE_HISTORY插入两条记录，Start_Date为Day1，End_Date为NULL
• Day2 Bob的Rate变更后：UPDATE语句将Bob的旧记录End_Date设为Day2，INSERT语句插入Bob的新记录，Start_Date为Day2，End_Date为NULL

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