Oracle中筛选无关联Deposit的重复Recipient行查询问题

现有表结构及数据 #

Recipient表 #

RecipientDeposit表 #

需求 #

筛选出Recipient表中editor_id和email均重复的行，且这些行的id_recipient未在RecipientDeposit表中存在对应记录。

已实现的基础查询 #

你已经能通过以下语句获取所有未在RecipientDeposit表中存在的Recipient行：

SELECT 
    re.id_recipient , re.editor_id, re.email 
FROM 
    recipient re 
WHERE 
    re.id_recipient NOT IN (SELECT rd.id_recipient 
                            FROM RecipientDeposit rd 
                            WHERE re.id_recipient = rd.id_recipient)

你尝试的查询验证 #

你写的这条查询是正确的，完全符合需求，逻辑如下：

1. 子查询先筛选出所有editor_id+email组合重复的分组（即该组合出现次数大于1）；
2. 通过JOIN关联原表，提取出这些重复分组下的所有行；
3. 最后通过NOT IN排除掉在RecipientDeposit表中存在的记录，得到最终符合要求的结果。

替代实现方案（窗口函数写法） #

也可以用窗口函数实现，逻辑更直观，结果一致：

SELECT id_recipient, editor_id, email
FROM (
    SELECT 
        re.*,
        COUNT(*) OVER(PARTITION BY editor_id, email) AS cnt
    FROM recipient re
    WHERE re.id_recipient NOT IN (SELECT id_recipient FROM RecipientDeposit)
) t
WHERE cnt > 1;

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