结合HAVING子句多表查询：获取重复阻塞邮箱的联系人详情

表结构 #

Contact表 #

BlockedEntries表 #

需求与现有问题 #

需要从Contact表中找出同时满足以下两个条件的记录，并返回完整联系人详情：

1. 邮箱存在于BlockedEntries表中
2. 该邮箱在Contact表中出现次数超过1次

现有查询语句仅能统计邮箱出现次数和关联ID集合，无法返回具体联系人信息：

SELECT email, COUNT(*)  as cc, GROUP_CONCAT( id  SEPARATOR '#') AS ContactIds 
FROM contacts 
where email IN (SELECT email FROM BlockedEntries)
GROUP BY email   
HAVING COUNT(*) > 1 

示例数据 #

Contact表数据 #

BlockedEntries表数据 #

期望结果 #

注：dfe@gmail.com在Contact表中仅出现1次，不纳入结果集

解决方案 #

方法1：子查询关联 #

先筛选出符合条件的邮箱集合，再关联原表获取完整数据：

SELECT c.Email, c.Id, c.FirstName, c.LastName, c.JobTitle
FROM Contact c
JOIN (
    -- 筛选：在封禁列表中，且在Contact表中出现次数>1的邮箱
    SELECT Email
    FROM Contact
    WHERE Email IN (SELECT Email FROM BlockedEntries)
    GROUP BY Email
    HAVING COUNT(*) > 1
) filtered_emails ON c.Email = filtered_emails.Email

方法2：窗口函数（性能更优） #

通过窗口函数一次性计算邮箱出现次数和封禁状态，直接过滤出目标数据：

SELECT Email, Id, FirstName, LastName, JobTitle
FROM (
    SELECT 
        Email, Id, FirstName, LastName, JobTitle,
        COUNT(*) OVER (PARTITION BY Email) AS email_count,
        EXISTS (SELECT 1 FROM BlockedEntries be WHERE be.Email = Contact.Email) AS is_blocked
    FROM Contact
) t
WHERE email_count > 1 AND is_blocked = 1

说明 #

• 方法1逻辑直观，适合数据量较小的场景
• 方法2无需额外关联操作，在大数据量下性能更出色

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