如何编写SQL查询通过匹配新旧值倒序追溯审计日志原始值

要从审计日志中通过NEW_VALUE和OLD_VALUE的链式关联倒序追溯到原始值，最直接的方法是使用递归公共表表达式（CTE），它能高效处理这种链式回溯的场景。

假设审计表结构 #

基于常见的账户审计表结构定义字段（如果你的表结构不同，只需替换对应字段名即可）：

• audit_id：唯一审计记录ID（用于排序或标识）
• account_id：关联的目标账户ID
• column_name：发生变更的列名（如果需要追溯特定字段）
• old_value：变更前的字段值
• new_value：变更后的字段值
• change_timestamp：变更发生的时间戳

核心SQL查询 #

已知目标账户ID为@target_account_id，要追溯的字段为'balance'，当前值为@current_value，查询如下：

WITH RECURSIVE audit_trace AS (
    -- 锚点：定位到当前值对应的最新变更记录
    SELECT 
        audit_id,
        account_id,
        column_name,
        old_value,
        new_value,
        change_timestamp,
        1 AS trace_level
    FROM account_audit
    WHERE 
        account_id = @target_account_id
        AND column_name = 'balance'
        AND new_value = @current_value
    ORDER BY change_timestamp DESC
    LIMIT 1

    UNION ALL

    -- 递归回溯：用当前记录的old_value匹配上一条记录的new_value
    SELECT 
        aa.audit_id,
        aa.account_id,
        aa.column_name,
        aa.old_value,
        aa.new_value,
        aa.change_timestamp,
        at.trace_level + 1
    FROM account_audit aa
    INNER JOIN audit_trace at 
        ON aa.account_id = at.account_id
        AND aa.column_name = at.column_name
        AND aa.new_value = at.old_value
)
-- 提取最原始的记录值
SELECT 
    -- 初始创建记录的old_value通常为NULL，此时原始值为new_value
    CASE 
        WHEN old_value IS NULL THEN new_value
        ELSE old_value 
    END AS original_value,
    change_timestamp AS original_change_time
FROM audit_trace
ORDER BY trace_level DESC
LIMIT 1;

关键逻辑说明 #

1. 锚点查询：先定位到匹配当前值的最新变更记录，确保从账户的当前状态开始回溯。通过ORDER BY change_timestamp DESC LIMIT 1避免同一值存在多条变更记录时的歧义。
2. 递归回溯：通过aa.new_value = at.old_value建立链式关联，每次回溯都找到上一次变更的记录，直到无法找到更早的关联记录（即到达原始状态）。
3. 提取原始值：递归结果中trace_level最大的记录就是最原始的状态。如果是账户的初始创建记录（old_value为NULL），则取new_value作为原始值。

特殊情况处理 #

• 如果当前值本身就是原始值（无任何变更记录）：锚点查询会返回空结果，此时可以通过COALESCE结合额外查询处理，例如：

  SELECT COALESCE(
      (SELECT CASE WHEN old_value IS NULL THEN new_value ELSE old_value END FROM audit_trace ORDER BY trace_level DESC LIMIT 1),
      @current_value
  ) AS original_value;
  

• 如果需要追溯所有字段的原始值：去掉column_name的过滤条件，在最终结果中按column_name分组提取即可。

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