SQL Server高效筛选特定记录：排除陈旧重复数据

核心筛选规则拆解 #

先把需求转化为清晰的筛选逻辑：

• 直接保留所有CODE = 'A'的记录
• 保留CODE = 'C'的NEW记录，同时排除该ACT对应的OLD记录
• 完全排除CODE = 'D'的记录，以及该ACT对应的OLD记录
• CODE为空（含NULL/空白字符）的OLD记录，仅当该ACT没有任何NEW记录时才保留

高效查询语句（百万级数据友好） #

用CTE+窗口函数实现，仅需扫描表一次，避免多次JOIN/EXISTS带来的性能损耗：

WITH act_summary AS (
    SELECT 
        *,
        -- 标记当前ACT是否存在CODE=C的NEW记录
        MAX(CASE WHEN [SET] = 'NEW' AND CODE = 'C' THEN 1 ELSE 0 END) OVER (PARTITION BY ACT) AS has_new_c,
        -- 标记当前ACT是否存在CODE=D的NEW记录
        MAX(CASE WHEN [SET] = 'NEW' AND CODE = 'D' THEN 1 ELSE 0 END) OVER (PARTITION BY ACT) AS has_new_d,
        -- 标记当前ACT是否有任何NEW记录
        MAX(CASE WHEN [SET] = 'NEW' THEN 1 ELSE 0 END) OVER (PARTITION BY ACT) AS has_any_new,
        -- 标记当前记录是否是CODE=A的记录
        CASE WHEN CODE = 'A' THEN 1 ELSE 0 END AS is_code_a
    FROM your_table_name -- 替换成你的实际表名
)
SELECT 
    ACT, STATUS, CODE, [SET], VALUE
FROM act_summary
WHERE
    -- 规则1：保留CODE=A的记录
    is_code_a = 1
    -- 规则2：保留CODE=C的NEW记录
    OR ([SET] = 'NEW' AND CODE = 'C')
    -- 规则4：保留无对应NEW记录的空CODE OLD记录
    OR ([SET] = 'OLD' AND ISNULL(LTRIM(RTRIM(CODE)), '') = '' AND has_any_new = 0 AND has_new_d = 0)
-- 排除被规则2、3命中的OLD记录
AND NOT ([SET] = 'OLD' AND (has_new_c = 1 OR has_new_d = 1));

性能优化建议（针对百万级数据） #

1. 建立复合覆盖索引
   创建包含分组、筛选和返回字段的索引，大幅减少逻辑读：

   CREATE NONCLUSTERED INDEX IX_YourTable_ACT_Set_Code 
   ON your_table_name (ACT, [SET], CODE) 
   INCLUDE (STATUS, VALUE); -- INCLUDE里放需要查询返回的列
   

2. 预处理CODE字段
   如果表中存在大量空白字符的CODE，建议先统一更新为NULL，这样可以把ISNULL(LTRIM(RTRIM(CODE)), '') = ''简化为CODE IS NULL，避免函数运算导致索引失效。
3. 验证索引有效性
   执行查询前打开统计IO，查看逻辑读是否下降：

   SET STATISTICS IO ON;
   

测试示例 #

用你提供的测试数据验证效果：

-- 创建临时测试表
CREATE TABLE #test_data (
    ACT INT,
    STATUS VARCHAR(20),
    CODE VARCHAR(1),
    [SET] VARCHAR(3),
    VALUE INT
);

-- 插入测试数据
INSERT INTO #test_data VALUES
(222, '', '', 'OLD', 1),
(333, '', '', 'OLD', 2),
(444, '', '', 'OLD', 3),
(111, 'ADDED', 'A', 'NEW', 4),
(222, 'CHANGED', 'C', 'NEW', 5),
(333, 'DELETED', 'D', 'NEW', 6);

-- 执行查询
WITH act_summary AS (
    SELECT 
        *,
        MAX(CASE WHEN [SET] = 'NEW' AND CODE = 'C' THEN 1 ELSE 0 END) OVER (PARTITION BY ACT) AS has_new_c,
        MAX(CASE WHEN [SET] = 'NEW' AND CODE = 'D' THEN 1 ELSE 0 END) OVER (PARTITION BY ACT) AS has_new_d,
        MAX(CASE WHEN [SET] = 'NEW' THEN 1 ELSE 0 END) OVER (PARTITION BY ACT) AS has_any_new,
        CASE WHEN CODE = 'A' THEN 1 ELSE 0 END AS is_code_a
    FROM #test_data
)
SELECT 
    ACT, STATUS, CODE, [SET], VALUE
FROM act_summary
WHERE
    is_code_a = 1
    OR ([SET] = 'NEW' AND CODE = 'C')
    OR ([SET] = 'OLD' AND ISNULL(LTRIM(RTRIM(CODE)), '') = '' AND has_any_new = 0 AND has_new_d = 0)
AND NOT ([SET] = 'OLD' AND (has_new_c = 1 OR has_new_d = 1));

-- 清理临时表
DROP TABLE #test_data;

执行后会得到你期望的结果：

ACT|STATUS |CODE|SET|VALUE
444|       |    |OLD|3
111|ADDED  |A   |NEW|4
222|CHANGED|C   |NEW|5

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