SQL如何对比两表数据差异并追加变更日期字段

你现有逻辑的核心框架已经可以定位到存在数据变更的行，只需要做两处调整即可满足需求：

• 在反透视后的结果集中直接追加GETDATE()函数取值，即可得到变更时间字段
• （可选但建议）补充旧表反透视逻辑做关联校验，避免把「整行因其他列变更被捞出、但当前状态列本来就是closed没有发生变化」的记录误判为变更

沿用原有筛选逻辑的最简写法 #

如果你不需要校验状态列的旧值，仅需要在现有返回结果基础上追加变更时间，直接在unpivotdiff的查询字段中增加时间字段即可，代码如下：

with differencescte as (
select * from #newdata
except
select * from #olddata
),
unpivotdiff as (
select 
    ID,
    Statuss,
    ChangedStatus,
    GETDATE() AS Dateofchanged -- 追加变更时间字段
from differencescte
UNPIVOT
(
    ChangedStatus for Statuss In (status1,status2,status3)
) as up
)
select * from unpivotdiff
where ChangedStatus <> 'open'

修正误判问题的严谨写法 #

上面的最简写法存在小缺陷：如果某一行有多个状态列发生变更，其中某一列的旧值本来就是closed，这一列会因为整行被EXCEPT捞出、且新值不是open，被误标记为发生了closed变更。如果需要精准捕获从非closed状态变更为closed状态的记录，可以用新旧表分别反透视后关联对比的写法：

with differencescte as (
select * from #newdata
except
select * from #olddata
),
unpivotnew as (
select 
    ID,
    Statuss,
    ChangedStatus,
    GETDATE() AS Dateofchanged
from differencescte
UNPIVOT
(
    ChangedStatus for Statuss In (status1,status2,status3)
) as up
),
unpivotold as (
select 
    ID,
    Statuss,
    OldStatus
from #olddata
UNPIVOT
(
    OldStatus for Statuss In (status1,status2,status3)
) as up
)
select 
    n.ID,
    n.Statuss,
    n.ChangedStatus,
    n.Dateofchanged
from unpivotnew n
inner join unpivotold o 
    on n.ID = o.ID and n.Statuss = o.Statuss
where 
    n.ChangedStatus = 'closed'
    and o.OldStatus <> 'closed' -- 过滤掉旧值已经是closed的无效记录

两种写法最终返回的结果集结构均为ID|Statuss|ChangedStatus|Dateofchanged，完全符合需求。

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