SQL Server：查询同一ID下仅col_a列发生变化的行

解决方案思路 #

根据需求，要找出同一id下仅col_a列发生变化的记录，核心是对比每条记录与同id的关联记录，判断是否只有col_a不同。以下提供两种适配不同场景的实现方式：

场景1：仅保留与前一条记录相比仅col_a变化的行（匹配你的示例输出） #

如果历史记录按修改顺序生成（可通过row_id或时间戳排序），使用LAG窗口函数获取前一行的非col_a列数据，对比判断即可：

WITH prev_record AS (
    SELECT 
        *,
        -- 生成所有非col_a列的哈希值，用于快速对比
        HASHBYTES('SHA2_256', (SELECT col_b, col_c, col_d FOR XML PATH(''))) AS current_non_col_a_hash,
        -- 获取前一行的非col_a列哈希值
        LAG(HASHBYTES('SHA2_256', (SELECT col_b, col_c, col_d FOR XML PATH('')))) 
            OVER (PARTITION BY id ORDER BY row_id) AS prev_non_col_a_hash,
        -- 获取前一行的col_a值
        LAG(col_a) OVER (PARTITION BY id ORDER BY row_id) AS prev_col_a
    FROM your_history_table -- 替换为你的表名
)
SELECT row_id, id, col_a, col_b -- 替换为你需要输出的列
FROM prev_record
WHERE 
    -- 非col_a列与前一行完全一致
    current_non_col_a_hash = prev_non_col_a_hash
    -- col_a列与前一行不同
    AND col_a != prev_col_a;

说明：

• 使用FOR XML PATH('')拼接所有非col_a列，再用HASHBYTES生成哈希值，避免逐个列写LAG，适配多列场景；
• 若列中存在NULL，这种拼接方式能正确区分（比CONCAT更可靠）；
• ORDER BY row_id需替换为实际排序字段（如修改时间戳modify_time），确保按记录生成顺序对比。

场景2：找出所有非col_a列相同、但col_a有多个取值的行 #

如果需要获取所有属于“非col_a列相同、仅col_a不同”分组的行（包括该分组的第一条记录），可以用窗口函数分组统计：

WITH grouped_stats AS (
    SELECT 
        *,
        -- 按id+所有非col_a列分组，统计该分组内不同col_a的数量
        COUNT(DISTINCT col_a) OVER (PARTITION BY id, col_b, col_c, col_d) AS col_a_distinct_count
    FROM your_history_table -- 替换为你的表名
)
SELECT row_id, id, col_a, col_b -- 替换为你需要输出的列
FROM grouped_stats
WHERE col_a_distinct_count > 1
ORDER BY id, row_id;

说明：

• PARTITION BY id, col_b, col_c, col_d需包含所有非col_a的列；
• 当分组内col_a的不同取值数大于1时，说明该分组内的行都是仅col_a变化产生的。

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