如何筛选TableA中符合特定规则的无效重复记录？

我有一张包含无效重复记录的TableA表，目前使用以下SQL查询获取重复记录：

SELECT Id,
       StartDate,
       EndDate,
       Rate,
       TDate,
       COUNT(*) AS Record
FROM TableA
GROUP BY Id,
         StartDate,
         EndDate,
         Rate,
         TDate
HAVING COUNT(*)>1

新需求规则 #

• 重复判定标准：仅当StartDate和EndDate完全相同时才判定为重复，即使TDate相同但时间部分不同也不算重复（例如Id4的3条Rate为'X'的记录中，仅2条是重复）。
• 无效重复规则：
  • 除'NP'外的其他Rate的重复记录，均视为无效；
  • Rate为'NP'的记录默认允许重复，仅当同一Id和TDate下同时存在其他Rate（如'X'）的重复记录时，'NP'的重复才视为无效。

示例数据 #

示例规则说明 #

• Id1：仅存在NP重复，无其他Rate重复，属于有效记录，不纳入结果；
• Id2：存在NP重复和P重复，P重复为无效，NP重复为有效，仅P重复记录纳入结果；
• Id3：存在NP、P、X重复，所有重复均为无效，全部纳入结果；
• Id4：存在P和X重复，均为无效，全部纳入结果。

期望结果 #

解决方案SQL #

WITH base_duplicates AS (
    -- 找出所有符合重复条件的记录分组
    SELECT 
        Id,
        StartDate,
        EndDate,
        Rate,
        TDate,
        COUNT(*) AS RecordCount
    FROM TableA
    GROUP BY Id, StartDate, EndDate, Rate, TDate
    HAVING COUNT(*) > 1
),
non_np_duplicate_groups AS (
    -- 提取存在非NP重复记录的Id+TDate组合
    SELECT DISTINCT Id, TDate
    FROM base_duplicates
    WHERE Rate != 'NP'
)
-- 筛选最终无效重复记录
SELECT 
    bd.Id,
    bd.StartDate,
    bd.EndDate,
    bd.Rate,
    bd.TDate
FROM base_duplicates bd
LEFT JOIN non_np_duplicate_groups ndg 
    ON bd.Id = ndg.Id AND bd.TDate = ndg.TDate
WHERE 
    -- 非NP的重复直接保留
    bd.Rate != 'NP'
    -- NP的重复仅当所在Id+TDate存在非NP重复时保留
    OR (bd.Rate = 'NP' AND ndg.Id IS NOT NULL);

代码解释 #

• base_duplicates：按原逻辑找出所有重复记录分组，确保仅StartDate和EndDate完全相同的记录被判定为重复；
• non_np_duplicate_groups：提取存在非NP重复记录的Id和TDate组合，用于标记需要纳入无效范围的NP重复；
• 最终查询：筛选非NP的重复记录，以及存在非NP重复的Id+TDate下的NP重复记录，完全符合需求规则。

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