SQL Server中如何校验同ID多行Classification值完整性并添加异常标记

核心实现逻辑 #

按ID分组校验每组下Classification字段是否完整覆盖1、2、3、4四个必填枚举值，缺任意值则整组标记为异常，采用窗口函数实现单表扫描计算，适配大数据量场景的性能要求。

可直接运行的SQL代码 #

SELECT
    ID,
    Classification,
    CASE
        -- 逐一枚举判断四个必填值是否在当前ID分组下存在
        WHEN MAX(CASE WHEN Classification = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY ID) = 1
         AND MAX(CASE WHEN Classification = 2 THEN 1 ELSE 0 END) OVER (PARTITION BY ID) = 1
         AND MAX(CASE WHEN Classification = 3 THEN 1 ELSE 0 END) OVER (PARTITION BY ID) = 1
         AND MAX(CASE WHEN Classification = 4 THEN 1 ELSE 0 END) OVER (PARTITION BY ID) = 1
        THEN 0 -- 四个值全覆盖，校验通过
        ELSE 1  -- 缺任意值，标记异常
    END AS Flag
FROM 替换为你的实际业务表名

方案说明 #

• 性能表现：窗口函数写法仅需对原表做一次扫描，不需要分组后二次JOIN原表，数据量越大性能优势越明显
• 逻辑严谨性：逐一枚举四个必填值做存在性判断，不会因为Classification出现1-4以外的脏值凑够去重计数，导致误判校验通过
• 兼容性：支持SQL Server 2012及以上所有主流版本

结果样例 #

基于提供的测试数据，执行后返回结果如下：

超大数据量优化建议 #

如果表数据量超过千万级，可提前为表创建ID字段的非聚集索引，将Classification设为包含列，可大幅降低查询时的IO开销，SQL代码无需调整。

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