SQL Server去重：无需CTE/row_number的单查询方案需求

前置假设 #

假设目标表名为YourTable，包含核心字段：

• TRID：待输出的待删除记录ID
• ACTID：分组去重的依据字段
• CD：规则判断字段
• CreateTime：用于判断记录新旧的时间字段（若实际用其他字段，替换即可）

清理规则回顾 #

1. CD=4的记录绝对不能删除
2. 同一ACTID下仅存在CD=0/2，或存在多条CD=4的记录时，保留最新记录，其余标记为待删除
3. 同一ACTID下同时存在CD=2和CD=4时，所有CD=2的记录都要删除

最终查询SQL #

SELECT t.TRID
FROM YourTable t
WHERE 
    -- 规则3：同ACTID下存在CD=4时，所有CD=2的记录直接标记删除
    (EXISTS (SELECT 1 FROM YourTable t2 WHERE t2.ACTID = t.ACTID AND t2.CD = 4) AND t.CD = 2)
    OR
    -- 处理非CD=4的记录，或多条CD=4的情况
    (
        t.CD != 4 
        OR 
        (t.CD = 4 AND EXISTS (SELECT 1 FROM YourTable t2 WHERE t2.ACTID = t.ACTID AND t2.CD = 4 AND t2.CreateTime > t.CreateTime))
    )
    -- 规则2：排除同分组内的最新记录
    AND NOT EXISTS (
        SELECT 1 
        FROM YourTable t2 
        WHERE t2.ACTID = t.ACTID 
        AND (
            -- 非CD=4的记录，保留同ACTID下最新的一条
            (t.CD != 4 AND t2.CreateTime = (SELECT MAX(CreateTime) FROM YourTable t3 WHERE t3.ACTID = t.ACTID))
            -- 多条CD=4的记录，仅保留同ACTID同CD分组下最新的一条
            OR (t.CD = 4 AND t2.CD = 4 AND t2.CreateTime = (SELECT MAX(CreateTime) FROM YourTable t3 WHERE t3.ACTID = t.ACTID AND t3.CD = 4))
        )
        AND t2.TRID = t.TRID
    )

逻辑说明 #

1. 第一分支直接命中规则3：只要同ACTID下存在CD=4的记录，所有CD=2的记录都被标记为待删除
2. 第二分支处理两类场景：
   • 非CD=4的记录，判断是否为同ACTID下的最新记录，不是则标记删除
   • 多条CD=4的记录，仅保留同分组内最新的一条，其余标记删除
3. 全程未使用CTE或ROW_NUMBER()函数，可直接嵌入JPA的@Query注解中使用

注意事项 #

• 若判断记录新旧的字段不是CreateTime，替换为实际业务中用于排序的字段（如自增ID、更新时间等）
• 建议给ACTID、CD、CreateTime字段建立联合索引，提升大表查询性能

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