SQL Server编写双数据快照对比及变更标记查询的咨询

1. 前置配置：定义可灵活修改的快照变量 #

你可以直接修改下方两个变量的赋值，指定任意需要对比的两个快照时间：

DECLARE @PriorSnapshotDT DATETIME = '填写你要对比的旧快照load_datetime值';
DECLARE @LatestSnapshotDT DATETIME = '填写你要对比的新快照load_datetime值';

2. 完整查询代码 #

假设你的数据表名为snapshot_table，唯一标识行的主键字段为row_id，其余业务字段为col1, col2, col3（可替换为你实际的表名、主键、业务字段）：

WITH old_snap AS (
    SELECT * FROM snapshot_table WHERE load_datetime = @PriorSnapshotDT
),
new_snap AS (
    SELECT * FROM snapshot_table WHERE load_datetime = @LatestSnapshotDT
)
SELECT
    -- 优先取新快照的字段值，删除行取旧快照的字段值
    ISNULL(new_snap.row_id, old_snap.row_id) AS row_id,
    ISNULL(new_snap.col1, old_snap.col1) AS col1,
    ISNULL(new_snap.col2, old_snap.col2) AS col2,
    ISNULL(new_snap.col3, old_snap.col3) AS col3,
    -- 新增要求的两个字段
    @PriorSnapshotDT AS PRIOR_SNAPSHOT_LOAD_DATETIME,
    CASE
        WHEN old_snap.row_id IS NULL THEN 'NEW'
        WHEN new_snap.row_id IS NULL THEN 'REMOVED'
        WHEN 
            ISNULL(old_snap.col1, '') <> ISNULL(new_snap.col1, '')
            OR ISNULL(old_snap.col2, 0) <> ISNULL(new_snap.col2, 0)
            OR ISNULL(old_snap.col3, '') <> ISNULL(new_snap.col3, '')
        THEN 'VALUE CHANGES'
        -- 无变更的行可按需标记为UNCHANGED或者加WHERE条件过滤
        ELSE 'UNCHANGED'
    END AS SNAPSHOT_CHANGE_STATUS
FROM old_snap
FULL OUTER JOIN new_snap ON old_snap.row_id = new_snap.row_id

3. 逻辑说明 #

• 采用FULL OUTER JOIN关联两份快照，能同时覆盖新增、删除、修改、无变更四种行场景，符合需求返回范围
• 行取值逻辑完全匹配需求：新快照存在的行返回新快照全字段值，仅旧快照存在的删除行返回旧快照全字段值
• 变更状态判断规则：
  • 仅新快照存在的行 → 标记NEW
  • 仅旧快照存在的行 → 标记REMOVED
  • 两份快照都存在、且任意业务字段值不同 → 标记VALUE CHANGES
• 字段对比时搭配ISNULL处理NULL值，避免SQL中NULL与任何值比较都返回FALSE的问题，可根据字段实际类型替换ISNULL的默认值

数据示例 #

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