SQL如何基于指定列对比调整版本变更过滤结果集

核心思路 #

通过窗口函数获取同索赔ID（claimId）下上一调整版本的对比字段值，判断版本间是否存在指定字段的变更，最终仅保留存在变更的索赔ID对应所有行。

静态SQL实现（对比字段固定时使用） #

当对比字段固定为ProcedureCode、PlaceOfService时，直接使用以下语句：

WITH version_compare AS (
    SELECT 
        *,
        -- 取同索赔ID下上一版本的对比字段值
        LAG(ProcedureCode) OVER(PARTITION BY claimId ORDER BY adjustmentVersion) AS prev_ProcedureCode,
        LAG(PlaceOfService) OVER(PARTITION BY claimId ORDER BY adjustmentVersion) AS prev_PlaceOfService
    FROM #ResultSet_fields
),
claim_change_flag AS (
    SELECT 
        claimId,
        -- 标记该索赔ID是否存在版本间字段变更
        MAX(CASE 
            WHEN prev_ProcedureCode IS NOT NULL AND ProcedureCode <> prev_ProcedureCode THEN 1
            WHEN prev_PlaceOfService IS NOT NULL AND PlaceOfService <> prev_PlaceOfService THEN 1
            ELSE 0 
        END) AS has_change
    FROM version_compare
    GROUP BY claimId
)
SELECT rf.* 
FROM #ResultSet_fields rf
INNER JOIN claim_change_flag cf ON rf.claimId = cf.claimId
WHERE cf.has_change = 1

动态SQL实现（适配@CompareFields动态传参场景） #

如果@CompareFields的取值会动态调整，使用动态SQL拼接逻辑即可通用适配：

DECLARE @sql NVARCHAR(MAX), @lag_cols NVARCHAR(MAX), @compare_rule NVARCHAR(MAX)

-- 拼接LAG函数取上一版本字段的逻辑
SELECT @lag_cols = STRING_AGG('LAG(' + value + ') OVER(PARTITION BY claimId ORDER BY adjustmentVersion) AS prev_' + value, ',')
FROM STRING_SPLIT(@CompareFields, ',')

-- 拼接字段变更判断规则
SELECT @compare_rule = STRING_AGG('prev_' + value + ' IS NOT NULL AND ' + value + ' <> prev_' + value, ' OR ')
FROM STRING_SPLIT(@CompareFields, ',')

SET @sql = N'
WITH version_compare AS (
    SELECT *, ' + @lag_cols + N'
    FROM #ResultSet_fields
),
claim_change_flag AS (
    SELECT claimId, MAX(CASE WHEN ' + @compare_rule + N' THEN 1 ELSE 0 END) AS has_change
    FROM version_compare
    GROUP BY claimId
)
SELECT rf.* 
FROM #ResultSet_fields rf
INNER JOIN claim_change_flag cf ON rf.claimId = cf.claimId
WHERE cf.has_change = 1
'

EXEC sp_executesql @sql

注意事项 #

• 如果对比字段允许为NULL，需要调整等值判断逻辑：SQL Server 2022及以上版本可以用IS NOT DISTINCT FROM替代<>，低版本可以用ISNULL(字段, 预设默认值)处理NULL值相等的场景。
• 如果仅需要保留发生变更的对应版本行（而非该索赔ID下所有行），去掉claim_change_flag分组统计逻辑，直接过滤version_compare中满足变更条件的行即可。

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