SQL Server历史表审计信息重组查询实现求助

嗨，我完全理解你的需求——把历史表里的全量快照记录，转换成每条只显示单个字段变更的清晰审计日志，这个在SQL Server里绝对能实现，我来一步步给你拆解怎么做：

首先，咱们得先理清楚核心逻辑：每个历史记录是某一时刻的全量数据快照，要找出变更，就得把当前快照和上一个更早的快照做对比，然后把每个变化的字段拆成单独的行。

具体实现代码 #

我用CTE（公共表表达式）配合窗口函数和逆透视的思路来写，代码的可读性和扩展性都不错：

WITH HistoryWithPrevious AS (
    SELECT 
        DataId,
        Time,
        Username,
        DataA,
        DataB,
        DataC,
        -- 用LAG函数获取同一条数据的上一个版本字段值
        LAG(DataA) OVER (PARTITION BY DataId ORDER BY Time ASC) AS PreviousDataA,
        LAG(DataB) OVER (PARTITION BY DataId ORDER BY Time ASC) AS PreviousDataB,
        LAG(DataC) OVER (PARTITION BY DataId ORDER BY Time ASC) AS PreviousDataC
    FROM 
        YourHistoryTable -- 替换成你实际的历史表名称
)
SELECT 
    DataId,
    Time,
    Username,
    ColumnName AS [Column],
    PreviousValue AS [Old value],
    CurrentValue AS [New value]
FROM 
    HistoryWithPrevious
CROSS APPLY (
    -- 把每个字段的新旧值转换成单独的行（逆透视操作）
    VALUES 
        ('DataA', PreviousDataA, DataA),
        ('DataB', PreviousDataB, DataB),
        ('DataC', PreviousDataC, DataC)
) AS Changes (ColumnName, PreviousValue, CurrentValue)
WHERE 
    -- 排除没有上一版本的初始记录
    PreviousValue IS NOT NULL
    -- 只保留字段值确实发生变化的行
    AND PreviousValue != CurrentValue
ORDER BY 
    Time DESC; -- 按时间倒序，最新变更排在最前面

代码逻辑解释 #

1. HistoryWithPrevious CTE：这里用LAG()窗口函数，按DataId分组（保证只对比同一条数据的历史版本）、按Time升序排序，这样每条记录都能拿到它上一个更早版本的字段值。
2. CROSS APPLY + VALUES：这一步是把原本的列（DataA/DataB/DataC）转换成行，每个字段的新旧值单独占一行，方便我们筛选出有变化的内容。
3. 筛选条件：PreviousValue IS NOT NULL去掉最开始的初始快照（它没有上一版本可以对比），PreviousValue != CurrentValue只保留真正有变更的字段记录。

测试你的示例数据 #

用你提供的历史表数据运行这段代码，得到的结果正好匹配你想要的格式：

额外注意事项 #

• 如果你的字段可能出现NULL值，那PreviousValue != CurrentValue会漏掉NULL和非NULL之间的变更，这时候可以改成ISNULL(PreviousValue, '') != ISNULL(CurrentValue, '')（根据字段类型调整默认占位值）。
• 以后如果新增了字段，只需要在VALUES块里加一行对应的字段即可，扩展性非常好。

备注：内容来源于stack exchange，提问作者kipy