如何通过对比不同时段数据找出数据表中变更字段？

单字段变更识别（以code=H01的fieldA为例） #

假设你的数据表名为data_table，字段包括date（日期）、code（唯一标识）、fieldA、fieldB等。要找出code=H01的fieldA在2023-01-10至2023-01-20间的变更，最直接的方式是用窗口函数LAG（获取上一个时段的字段值）做对比：

SELECT
  code,
  date,
  fieldA AS current_fieldA,
  LAG(fieldA) OVER (PARTITION BY code ORDER BY date) AS previous_fieldA,
  -- 标记是否变更，用COALESCE处理NULL值对比失效问题
  CASE
    WHEN COALESCE(fieldA, 'NULL_PLACEHOLDER') != COALESCE(LAG(fieldA) OVER (PARTITION BY code ORDER BY date), 'NULL_PLACEHOLDER') 
    THEN '已变更'
    ELSE '未变更'
  END AS fieldA_change_status
FROM data_table
WHERE code = 'H01'
  AND date BETWEEN '2023-01-10' AND '2023-01-20'
ORDER BY date;

这段SQL会按日期排序，对比每个日期的fieldA与上一个日期的值，直接定位从valueA1到valueA2的变更节点。

多字段批量变更识别 #

如果需要同时检测fieldA、fieldB甚至更多字段的变更，有两种实用方案：

方案1：逐个字段手动判断（适用于字段数量少的场景） #

对每个需要检测的字段重复LAG对比逻辑，再用字符串拼接汇总所有变更字段：

WITH change_records AS (
  SELECT
    code,
    date,
    -- 逐个字段判断变更
    CASE WHEN COALESCE(fieldA, 'NULL_PLACEHOLDER') != COALESCE(LAG(fieldA) OVER (PARTITION BY code ORDER BY date), 'NULL_PLACEHOLDER') 
         THEN 'fieldA' END AS fa_change,
    CASE WHEN COALESCE(fieldB, 'NULL_PLACEHOLDER') != COALESCE(LAG(fieldB) OVER (PARTITION BY code ORDER BY date), 'NULL_PLACEHOLDER') 
         THEN 'fieldB' END AS fb_change,
    -- 保留变更前后的值
    LAG(fieldA) OVER (PARTITION BY code ORDER BY date) AS prev_fieldA,
    fieldA AS curr_fieldA,
    LAG(fieldB) OVER (PARTITION BY code ORDER BY date) AS prev_fieldB,
    fieldB AS curr_fieldB
  FROM data_table
  WHERE date BETWEEN '2023-01-10' AND '2023-01-20'
)
SELECT
  code,
  date,
  -- 拼接所有变更字段
  TRIM(TRAILING ',' FROM CONCAT(
    IF(fa_change IS NOT NULL, fa_change, ''),
    IF(fb_change IS NOT NULL, ',' || fb_change, '')
  )) AS changed_fields,
  -- 显示每个字段的变更详情
  CONCAT('fieldA: ', prev_fieldA, ' -> ', curr_fieldA) AS fieldA_change_detail,
  CONCAT('fieldB: ', prev_fieldB, ' -> ', curr_fieldB) AS fieldB_change_detail
FROM change_records
ORDER BY code, date;

执行后会在一行中展示该时段所有发生变更的字段，以及每个字段的前后值变化。

方案2：动态SQL批量处理（适用于字段数量多的场景） #

如果字段数量较多，手动写每个字段的逻辑效率太低，可以用动态SQL自动生成对比逻辑（以MySQL为例）：

-- 第一步：获取所有需要对比的字段（排除date和code）
SET @target_fields = (
  SELECT GROUP_CONCAT(DISTINCT COLUMN_NAME)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = '你的数据库名'
    AND TABLE_NAME = 'data_table'
    AND COLUMN_NAME NOT IN ('date', 'code')
);

-- 第二步：动态生成SQL语句
SET @sql = CONCAT('
WITH ordered_data AS (
  SELECT
    code,
    date,
    ', GROUP_CONCAT('LAG(', COLUMN_NAME, ') OVER (PARTITION BY code ORDER BY date) AS prev_', COLUMN_NAME SEPARATOR ', '), '
    ', GROUP_CONCAT(', ', COLUMN_NAME SEPARATOR ''), '
  FROM data_table
  WHERE date BETWEEN ''2023-01-10'' AND ''2023-01-20''
)
SELECT
  code,
  date,
  -- 拼接所有变更字段
  TRIM(TRAILING '','' FROM CONCAT(',
    GROUP_CONCAT('IF(COALESCE(', COLUMN_NAME, ', ''NULL_PLACEHOLDER'') != COALESCE(prev_', COLUMN_NAME, ', ''NULL_PLACEHOLDER''), ''', COLUMN_NAME, ''', '''') SEPARATOR '', '''), '
  )) AS changed_fields,
  -- 生成每个字段的变更详情
  ', GROUP_CONCAT('CONCAT('''', COLUMN_NAME, '': '', prev_', COLUMN_NAME, ''', -> '', ', COLUMN_NAME, ') AS ', COLUMN_NAME, '_detail' SEPARATOR ', '), '
FROM ordered_data
ORDER BY code, date;
');

-- 第三步：执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

这段代码会自动读取表中所有非date、code的字段，生成对应的LAG对比逻辑，无需手动维护每个字段的判断语句，适合字段数量多或字段可能变动的场景。

内容的提问来源于stack exchange，提问作者罗简单