MySQL一对多关联筛选：无DEAD通话且关联≥6个lead_id的手机号

要同时筛选出无任何DEAD状态通话且在calls表中关联至少6个不同lead_id的手机号，可以在原查询逻辑基础上增加分组统计规则，以下是几种实用写法：

方法一：原查询直接扩展（推荐） #

SELECT l.phone_number
FROM leads l
JOIN calls c ON l.lead_id = c.lead_id
WHERE NOT EXISTS (
    SELECT 1
    FROM calls c_dead
    WHERE c_dead.lead_id IN (
        SELECT lead_id FROM leads WHERE phone_number = l.phone_number
    )
    AND c_dead.status = 'DEAD'
)
GROUP BY l.phone_number
HAVING COUNT(DISTINCT l.lead_id) >= 6;

逻辑说明： #

1. 保留原有的NOT EXISTS子句，确保当前手机号对应的所有lead_id都没有产生过DEAD状态的通话；
2. 通过JOIN calls关联通话记录，按phone_number分组；
3. 用COUNT(DISTINCT l.lead_id)统计该手机号关联的、在calls表中出现的不同lead_id数量，最后通过HAVING筛选出数量≥6的结果。

方法二：分步筛选（逻辑更清晰） #

先筛选出所有无DEAD通话的手机号，再从中过滤符合lead_id数量要求的：

SELECT valid_phones.phone_number
FROM (
    -- 第一步：获取所有无DEAD通话的手机号
    SELECT DISTINCT l.phone_number
    FROM leads l
    WHERE NOT EXISTS (
        SELECT 1
        FROM calls c
        WHERE c.lead_id IN (
            SELECT lead_id FROM leads WHERE phone_number = l.phone_number
        )
        AND c.status = 'DEAD'
    )
) AS valid_phones
JOIN leads l ON valid_phones.phone_number = l.phone_number
JOIN calls c ON l.lead_id = c.lead_id
GROUP BY valid_phones.phone_number
HAVING COUNT(DISTINCT l.lead_id) >= 6;

方法三：优化性能（大数据量场景） #

将原查询中的IN子句替换为JOIN，减少嵌套层级，提升查询效率：

SELECT l.phone_number
FROM leads l
JOIN calls c ON l.lead_id = c.lead_id
WHERE NOT EXISTS (
    SELECT 1
    FROM calls c_dead
    JOIN leads l_dead ON c_dead.lead_id = l_dead.lead_id
    WHERE l_dead.phone_number = l.phone_number
    AND c_dead.status = 'DEAD'
)
GROUP BY l.phone_number
HAVING COUNT(DISTINCT l.lead_id) >= 6;

注意：使用COUNT(DISTINCT)是为了避免同一lead_id在calls表中有多条记录时重复计数，确保统计的是不同lead_id的真实数量。

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