如何从已汇总的审计表中拆分Insert、Amend、Delete三类变更类型

最优实现方案 #

你当前的写法需要为每个属性重复编写多段CASE判断逻辑，属性数量多了之后维护成本极高，我们可以通过列拆行+按变更类型聚合的思路大幅优化可扩展性，后续新增属性仅需要补充2处配置即可，不需要修改核心逻辑。

核心逻辑如下：

1. 用CROSS APPLY + VALUES将每个属性的新旧值拆分为独立行，不用再为每个属性重复写判断逻辑
2. 对每个属性的新旧值单独判断变更类型，过滤掉无变更的属性
3. 按请求ID和变更类型分组聚合，将对应属性的值填回目标列，无变更的属性自动为NULL

优化后的查询代码如下：

SELECT 
    r.RequestID,
    ca.Change,
    MAX(CASE WHEN v.ValName = 'Value1' THEN v.OldVal END) AS Value1Old,
    MAX(CASE WHEN v.ValName = 'Value1' THEN v.NewVal END) AS Value1New,
    MAX(CASE WHEN v.ValName = 'Value2' THEN v.OldVal END) AS Value2Old,
    MAX(CASE WHEN v.ValName = 'Value2' THEN v.NewVal END) AS Value2New
-- 新增属性仅需要在此处添加对应的两行CASE判断即可，例如Value3就新增Value3Old、Value3New两行
FROM Requests r
-- 拆分每个属性为独立行
CROSS APPLY (
    VALUES
        ('Value1', r.Value1Old, r.Value1New),
        ('Value2', r.Value2Old, r.Value2New)
    -- 新增属性仅需要在此处加一行配置，格式为('ValueN', r.ValueNOld, r.ValueNNew)
) AS v(ValName, OldVal, NewVal)
-- 判断当前属性的变更类型
CROSS APPLY (
    SELECT CASE
        WHEN OldVal IS NULL AND NewVal IS NOT NULL THEN 'Insert'
        WHEN NewVal = 0 AND OldVal IS NOT NULL THEN 'Delete'
        WHEN OldVal IS NOT NULL AND NewVal > 0 AND OldVal <> NewVal THEN 'Amend'
    END AS Change
) AS ca
-- 过滤无变更的属性
WHERE ca.Change IS NOT NULL
GROUP BY r.RequestID, ca.Change
-- 按请求ID和变更类型排序，和预期结果顺序一致
ORDER BY r.RequestID, 
    CASE ca.Change WHEN 'Insert' THEN 1 WHEN 'Amend' THEN 2 WHEN 'Delete' THEN 3 END

该查询返回结果和你给出的预期结果完全一致，如果你要扩展到15个属性，只需要在注释标注的两处位置补充对应属性的配置即可，不需要修改其他逻辑，维护成本极低。

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