如何在工单Status列变更时插入新行并保留历史记录

核心思路 #

要同时实现新工单插入和状态变更历史留存，关键是先定位工单表中每个ID的最新状态，再与临时表的记录做对比，筛选出需要插入的两类数据：

• 工单表中从未出现过的ID（全新工单）
• 已存在ID，但临时表中的Status与工单表最新Status不一致（状态变更）

具体SQL实现 #

方法1：使用CTE获取每个ID的最新状态

WITH LatestTickets AS (
    SELECT 
        ID, 
        Status,
        -- 按Insert_date倒序，取每个ID的最新记录
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Insert_date DESC) AS RowNum
    FROM dbo.Tickets
)
INSERT INTO dbo.Tickets (ID, Created, Status, Insert_date)
SELECT 
    s.ID, 
    s.Created, 
    s.Status, 
    GETDATE() -- 插入时记录当前时间作为Insert_date
FROM dbo.Tickets_Staging s
LEFT JOIN LatestTickets lt ON s.ID = lt.ID
WHERE 
    -- 条件1：ID从未在工单表中出现
    lt.ID IS NULL
    -- 条件2：ID存在，但状态与最新记录不一致
    OR (lt.RowNum = 1 AND s.Status != lt.Status);

方法2：使用EXISTS子查询简化逻辑

如果不需要额外处理最新记录的其他字段，也可以直接用子查询判断最新状态：

INSERT INTO dbo.Tickets (ID, Created, Status, Insert_date)
SELECT 
    s.ID, 
    s.Created, 
    s.Status, 
    GETDATE()
FROM dbo.Tickets_Staging s
WHERE 
    -- 新ID：工单表中无此ID
    NOT EXISTS (SELECT 1 FROM dbo.Tickets t WHERE t.ID = s.ID)
    -- 状态变更：存在ID，但最新状态不等于临时表状态
    OR EXISTS (
        SELECT 1 
        FROM dbo.Tickets t 
        WHERE t.ID = s.ID
        AND t.Status != s.Status
        AND t.Insert_date = (SELECT MAX(Insert_date) FROM dbo.Tickets WHERE ID = s.ID)
    );

关键说明 #

• ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Insert_date DESC)：给每个ID的记录按插入时间排序，序号为1的就是最新记录
• GETDATE()：插入新记录时自动记录当前时间作为Insert_date，保证历史记录的时间线准确
• 两种方法都能同时覆盖新工单插入和状态变更场景，可根据表结构和性能需求选择

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