使用WHERE NOT EXISTS和LAG实现SQL Server表格增量更新的问题

问题分析 #

你当前用NOT EXISTS的逻辑是检查HRTable中是否存在任意一条相同EmpID+Category+Grade的记录，这就导致当员工职位从PT/P10变为FT/F10，再变回PT/P10时，因为HRTable里已经有过PT/P10的记录，新的PT/P10记录就插不进去了。但你实际需要的是跟踪状态的变化——只要当前记录和该员工的最新状态不同，就应该插入，不管之前是否有过相同状态。

完全可以用LAG函数来解决这个场景，下面是具体的实现思路和代码：

用LAG函数结合最新记录对比的解决方案 #

我们需要分两步处理：先筛选出ImportTable中自身状态有变化的记录，再和HRTable中该员工的最新记录对比，最终只插入真正有变化的行。

步骤1：过滤ImportTable内的重复状态记录 #

先对ImportTable按员工ID分组、按导入日期排序，用LAG函数抓取同一员工的上一条记录的职位信息，筛选出状态发生变化的记录（包括每个员工的第一条记录）：

WITH ImportChanges AS (
    SELECT 
        IMP_ImportDate,
        IMP_EmpID,
        IMP_Employee,
        IMP_Category,
        IMP_Grade,
        -- 获取同一员工上一条记录的Category和Grade
        LAG(IMP_Category) OVER (PARTITION BY IMP_EmpID ORDER BY IMP_ImportDate) AS Prev_Category,
        LAG(IMP_Grade) OVER (PARTITION BY IMP_EmpID ORDER BY IMP_ImportDate) AS Prev_Grade
    FROM ImportTable
),
FilteredImport AS (
    SELECT 
        IMP_ImportDate,
        IMP_EmpID,
        IMP_Employee,
        IMP_Category,
        IMP_Grade
    FROM ImportChanges
    -- 保留第一条记录，或者职位信息发生变化的记录
    WHERE 
        Prev_Category IS NULL 
        OR Prev_Category != IMP_Category 
        OR Prev_Grade != IMP_Grade
)

步骤2：与HRTable的最新记录对比，插入变更数据 #

接下来把筛选后的Import记录，和HRTable中每个员工的最新记录对比，只有当前记录的职位信息和最新记录不同时，才执行插入：

INSERT INTO HRTable (HR_ImportDate, HR_EmpID, HR_Employee, HR_Category, HR_Grade)
SELECT 
    f.IMP_ImportDate,
    f.IMP_EmpID,
    f.IMP_Employee,
    f.IMP_Category,
    f.IMP_Grade
FROM FilteredImport f
LEFT JOIN (
    -- 抓取HRTable中每个员工的最新记录（按导入日期降序取第一条）
    SELECT 
        HR_EmpID,
        HR_Category,
        HR_Grade,
        ROW_NUMBER() OVER (PARTITION BY HR_EmpID ORDER BY HR_ImportDate DESC) AS rn
    FROM HRTable
) latest ON f.IMP_EmpID = latest.HR_EmpID AND latest.rn = 1
WHERE 
    -- 如果HRTable中没有该员工的记录，直接插入
    latest.HR_EmpID IS NULL
    -- 或者有记录，但职位信息不同
    OR latest.HR_Category != f.IMP_Category
    OR latest.HR_Grade != f.IMP_Grade;

完整SQL语句 #

把两部分整合起来，完整的插入代码如下：

WITH ImportChanges AS (
    SELECT 
        IMP_ImportDate,
        IMP_EmpID,
        IMP_Employee,
        IMP_Category,
        IMP_Grade,
        LAG(IMP_Category) OVER (PARTITION BY IMP_EmpID ORDER BY IMP_ImportDate) AS Prev_Category,
        LAG(IMP_Grade) OVER (PARTITION BY IMP_EmpID ORDER BY IMP_ImportDate) AS Prev_Grade
    FROM ImportTable
),
FilteredImport AS (
    SELECT 
        IMP_ImportDate,
        IMP_EmpID,
        IMP_Employee,
        IMP_Category,
        IMP_Grade
    FROM ImportChanges
    WHERE 
        Prev_Category IS NULL 
        OR Prev_Category != IMP_Category 
        OR Prev_Grade != IMP_Grade
)
INSERT INTO HRTable (HR_ImportDate, HR_EmpID, HR_Employee, HR_Category, HR_Grade)
SELECT 
    f.IMP_ImportDate,
    f.IMP_EmpID,
    f.IMP_Employee,
    f.IMP_Category,
    f.IMP_Grade
FROM FilteredImport f
LEFT JOIN (
    SELECT 
        HR_EmpID,
        HR_Category,
        HR_Grade,
        ROW_NUMBER() OVER (PARTITION BY HR_EmpID ORDER BY HR_ImportDate DESC) AS rn
    FROM HRTable
) latest ON f.IMP_EmpID = latest.HR_EmpID AND latest.rn = 1
WHERE 
    latest.HR_EmpID IS NULL
    OR latest.HR_Category != f.IMP_Category
    OR latest.HR_Grade != f.IMP_Grade;

方案效果验证 #

针对你给出的示例数据：

1. FilteredImport会先筛选出20/3（第一条记录）、21/3（职位变更）、23/4（职位从FT/F10变回PT/P10），而22/4、24-27/4的重复状态记录会被过滤；
2. 再和HRTable的最新记录对比：
   • 20/3的记录插入后，HRTable中John的最新状态是PT/P10；
   • 21/3的记录和最新状态不同，插入；
   • 23/4的记录和HRTable中最新的FT/F10不同，插入；
     最终正好得到你需要的目标行。

额外优化建议 #

• 确保IMP_ImportDate是日期类型，避免字符串日期排序错误（如果是varchar类型，建议转换为date后再排序）；
• 给HRTable的HR_EmpID和HR_ImportDate创建联合索引，提升获取最新记录的查询效率。

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