MySQL中通过SELECT查询合并重复联系人数据的实现

针对你需要合并通过email或phone关联的分散联系人记录的需求，不能直接用简单的DISTINCT或GROUP BY，因为需要处理间接关联的情况（比如A与B共享email，B与C共享phone，三者需归为同一联系人）。以下是通用的实现思路和不同数据库的代码示例：

核心思路 #

1. 识别关联组：用递归CTE将所有通过email/phone直接或间接关联的记录归为同一组。
2. 聚合字段：对每个组内的非空字段进行合并，生成完整的联系人记录。

PostgreSQL 实现代码 #

WITH RECURSIVE contact_groups AS (
    -- 初始化：每条有效记录作为独立组
    SELECT 
        id, 
        name, 
        email, 
        phone,
        id AS group_id
    FROM contacts
    WHERE email IS NOT NULL OR phone IS NOT NULL -- 过滤无任何联系方式的记录
    UNION ALL
    -- 递归关联：合并有相同email/phone的记录到同一组
    SELECT 
        c.id, 
        c.name, 
        c.email, 
        c.phone,
        cg.group_id
    FROM contacts c
    JOIN contact_groups cg ON 
        (c.email = cg.email AND c.email IS NOT NULL) 
        OR (c.phone = cg.phone AND c.phone IS NOT NULL)
    WHERE c.id NOT IN (SELECT id FROM contact_groups)
),
grouped_contacts AS (
    -- 聚合组内字段，生成完整记录
    SELECT
        group_id,
        MAX(name) AS name, -- 取组内非空的name（可根据业务调整为MIN或其他逻辑）
        STRING_AGG(DISTINCT email, ', ') FILTER (WHERE email IS NOT NULL) AS email, -- 合并所有非空且唯一的email
        STRING_AGG(DISTINCT phone, ', ') FILTER (WHERE phone IS NOT NULL) AS phone -- 合并所有非空且唯一的phone
    FROM contact_groups
    GROUP BY group_id
)
SELECT name, email, phone
FROM grouped_contacts;

MySQL 实现代码 #

MySQL不支持FILTER语法，改用GROUP_CONCAT处理字段合并：

WITH RECURSIVE contact_groups AS (
    SELECT 
        id, 
        name, 
        email, 
        phone,
        id AS group_id
    FROM contacts
    WHERE email IS NOT NULL OR phone IS NOT NULL
    UNION ALL
    SELECT 
        c.id, 
        c.name, 
        c.email, 
        c.phone,
        cg.group_id
    FROM contacts c
    JOIN contact_groups cg ON 
        (c.email = cg.email AND c.email IS NOT NULL) 
        OR (c.phone = cg.phone AND c.phone IS NOT NULL)
    WHERE c.id NOT IN (SELECT id FROM contact_groups)
),
grouped_contacts AS (
    SELECT
        group_id,
        MAX(name) AS name,
        GROUP_CONCAT(DISTINCT email SEPARATOR ', ') AS email,
        GROUP_CONCAT(DISTINCT phone SEPARATOR ', ') AS phone
    FROM contact_groups
    WHERE email IS NOT NULL OR phone IS NOT NULL
    GROUP BY group_id
)
SELECT name, email, phone
FROM grouped_contacts;

SQL Server 实现代码 #

WITH RECURSIVE contact_groups AS (
    SELECT 
        id, 
        name, 
        email, 
        phone,
        id AS group_id
    FROM contacts
    WHERE email IS NOT NULL OR phone IS NOT NULL
    UNION ALL
    SELECT 
        c.id, 
        c.name, 
        c.email, 
        c.phone,
        cg.group_id
    FROM contacts c
    JOIN contact_groups cg ON 
        (c.email = cg.email AND c.email IS NOT NULL) 
        OR (c.phone = cg.phone AND c.phone IS NOT NULL)
    WHERE c.id NOT IN (SELECT id FROM contact_groups)
),
grouped_contacts AS (
    SELECT
        group_id,
        MAX(name) AS name,
        STRING_AGG(DISTINCT email, ', ') AS email,
        STRING_AGG(DISTINCT phone, ', ') AS phone
    FROM contact_groups
    WHERE email IS NOT NULL OR phone IS NOT NULL
    GROUP BY group_id
)
SELECT name, email, phone
FROM grouped_contacts;

关键细节说明 #

• 递归CTE会处理所有间接关联的记录，确保属于同一联系人的分散数据被合并。
• 如果组内存在多个不同的name，MAX(name)会取字典序最大的值，你可以根据业务需求替换为MIN(name)或统计出现次数最多的name。
• 若只需要保留一个联系方式（而非合并多个），可以把STRING_AGG替换为MAX(email)或MIN(email)。

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