如何在CTE中过滤满足特定条件的hash_key2重复记录？

可以通过两种简洁的方式实现需求：

方法一：用窗口函数标记重复项 #

借助窗口函数直接计算每个hash_key2的全局出现次数，再按规则筛选记录：

h_pit_cleaned as (
    select 
        *
    from (
        select 
            *,
            count(*) over (partition by hash_key2) as hash_key2_count
        from h_pit
    ) t
    -- 保留两类记录：
    -- 1. 不属于「hash_key1='FFFFFF'且business_key为空」的所有记录
    -- 2. 属于该类但hash_key2仅出现一次的记录
    where not (hash_key1 = 'FFFFFF' and business_key is null)
       or (hash_key1 = 'FFFFFF' and business_key is null and hash_key2_count = 1)
)

方法二：关联重复hash_key2列表排除目标记录 #

基于你已写出的重复hash_key2查询，通过关联操作剔除不符合要求的记录：

-- 先定义存储重复hash_key2的CTE
duplicate_hash_key2 as (
    select hash_key2
    from h_pit
    group by hash_key2
    having count(*) > 1
),
h_pit_cleaned as (
    select 
        hp.*
    from h_pit hp
    left join duplicate_hash_key2 dhk on hp.hash_key2 = dhk.hash_key2
    -- 筛选逻辑：要么不是目标类记录，要么是目标类但hash_key2不在重复列表中
    where not (hp.hash_key1 = 'FFFFFF' and hp.business_key is null)
       or (hp.hash_key1 = 'FFFFFF' and hp.business_key is null and dhk.hash_key2 is null)
)

结果验证 #

针对你提供的示例数据：

• 记录1、3：不属于目标类，直接保留
• 记录7：属于目标类且hash_key2=456重复，被剔除
• 记录9：属于目标类但hash_key2=888唯一，被保留

最终h_pit_cleaned将包含记录1、3、9。

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