查询指定PrimaryId对应行各字段的最后更新时间与操作人

示例历史表 #

需求说明 #

• 针对指定PrimaryId（示例为56），获取ValA到ValE每个字段的最后更新操作人和更新时间
• 规则：
  • 字段首次录入非NULL值视为创建，需记录该操作
  • 后续将字段设为NULL属于有效变更，需记录
  • 字段始终为NULL时，返回NULL的更新时间和操作人

原有方案的问题 #

• 当字段始终为NULL时，对应CTE无数据，无法返回预期的NULL结果
• 多字段查询时，每个字段都需要重复编写CTE，代码冗余且关联逻辑存在问题

简洁解决方案 #

通过窗口函数LAG比较每条记录与前一条记录的字段值差异，标记每个字段的变更记录，再筛选每个字段的最后一次变更，最后聚合得到结果：

WITH FieldChanges AS (
    SELECT
        PrimaryId,
        UpdatedBy,
        UpdatedOn,
        -- 标记每个字段是否发生变更（首次非NULL也视为变更）
        CASE WHEN LAG(ValA) OVER (PARTITION BY PrimaryId ORDER BY UpdatedOn) != ValA 
                OR (LAG(ValA) OVER (PARTITION BY PrimaryId ORDER BY UpdatedOn) IS NULL AND ValA IS NOT NULL)
             THEN 1 ELSE 0 END AS ValAChanged,
        CASE WHEN LAG(ValB) OVER (PARTITION BY PrimaryId ORDER BY UpdatedOn) != ValB 
                OR (LAG(ValB) OVER (PARTITION BY PrimaryId ORDER BY UpdatedOn) IS NULL AND ValB IS NOT NULL)
             THEN 1 ELSE 0 END AS ValBChanged,
        CASE WHEN LAG(ValC) OVER (PARTITION BY PrimaryId ORDER BY UpdatedOn) != ValC 
                OR (LAG(ValC) OVER (PARTITION BY PrimaryId ORDER BY UpdatedOn) IS NULL AND ValC IS NOT NULL)
             THEN 1 ELSE 0 END AS ValCChanged,
        CASE WHEN LAG(ValD) OVER (PARTITION BY PrimaryId ORDER BY UpdatedOn) != ValD 
                OR (LAG(ValD) OVER (PARTITION BY PrimaryId ORDER BY UpdatedOn) IS NULL AND ValD IS NOT NULL)
             THEN 1 ELSE 0 END AS ValDChanged,
        CASE WHEN LAG(ValE) OVER (PARTITION BY PrimaryId ORDER BY UpdatedOn) != ValE 
                OR (LAG(ValE) OVER (PARTITION BY PrimaryId ORDER BY UpdatedOn) IS NULL AND ValE IS NOT NULL)
             THEN 1 ELSE 0 END AS ValEChanged
    FROM [table]
    WHERE PrimaryId = @PrimaryId
),
LastChanges AS (
    SELECT
        PrimaryId,
        -- 获取每个字段最后一次变更的时间和操作人
        MAX(CASE WHEN ValAChanged = 1 THEN UpdatedOn END) AS ValAUpdatedOn,
        MAX(CASE WHEN ValAChanged = 1 THEN UpdatedBy END) AS ValAUpdatedBy,
        MAX(CASE WHEN ValBChanged = 1 THEN UpdatedOn END) AS ValBUpdatedOn,
        MAX(CASE WHEN ValBChanged = 1 THEN UpdatedBy END) AS ValBUpdatedBy,
        MAX(CASE WHEN ValCChanged = 1 THEN UpdatedOn END) AS ValCUpdatedOn,
        MAX(CASE WHEN ValCChanged = 1 THEN UpdatedBy END) AS ValCUpdatedBy,
        MAX(CASE WHEN ValDChanged = 1 THEN UpdatedOn END) AS ValDUpdatedOn,
        MAX(CASE WHEN ValDChanged = 1 THEN UpdatedBy END) AS ValDUpdatedBy,
        MAX(CASE WHEN ValEChanged = 1 THEN UpdatedOn END) AS ValEUpdatedOn,
        MAX(CASE WHEN ValEChanged = 1 THEN UpdatedBy END) AS ValEUpdatedBy
    FROM FieldChanges
    GROUP BY PrimaryId
)
SELECT * FROM LastChanges;

方案说明 #

1. FieldChanges CTE：使用LAG函数获取每个字段的前一次值，对比当前值，标记该字段是否发生变更（包括首次设置非NULL值的情况）
2. LastChanges CTE：通过聚合函数MAX筛选每个字段最后一次变更的记录，未发生过变更的字段会自动返回NULL
3. 无需为每个字段单独编写CTE，代码更简洁，且能正确处理始终为NULL的字段

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