MySQL三表关联查询异常：添加appointments表后结果缺失

问题原因分析 #

直接LEFT JOIN appointments后结果从2条变为1条，核心原因是未先聚合预约数据导致行膨胀，后续展示时误将同姓名的重复客户合并：如果某个客户有多个预约，直接关联会生成多行记录，若你用GROUP BY name来聚合，就会把同姓名的不同客户合并成一条，丢失原本的重复客户条目。

正确的查询SQL #

先通过子查询统计每个客户的预约数，再关联重复客户查询，确保每个客户对应一行结果：

SELECT 
    c.id AS client_id,
    c.name,
    b.business_name,
    COALESCE(ap.appointment_count, 0) AS appointment_count,
    -- 标记保留/待删除客户（以同姓名同企业下最小ID为保留对象）
    CASE WHEN c.id = MIN(c.id) OVER (PARTITION BY c.name, c.business_id) 
         THEN '保留' ELSE '待删除' END AS status
FROM clients c
INNER JOIN business b 
    ON c.business_id = b.id
LEFT JOIN (
    -- 预聚合每个客户的预约数，避免行膨胀
    SELECT client_id, COUNT(*) AS appointment_count
    FROM appointments
    GROUP BY client_id
) ap 
    ON c.id = ap.client_id
-- 筛选出存在重复的客户
WHERE EXISTS (
    SELECT 1 
    FROM clients c2
    WHERE c2.name = c.name 
      AND c2.business_id = c.business_id
      AND c2.id != c.id
)
ORDER BY c.name, c.business_id, c.id;

关键细节 #

• 用EXISTS筛选重复客户，确保只返回存在同姓名同企业的客户
• 子查询ap预统计预约数，避免直接关联导致的行重复
• 通过窗口函数MIN(c.id) OVER (...)标记保留客户，为后续迁移操作做准备

后续迁移预约+删除重复客户的SQL #

1. 迁移待删除客户的预约到保留客户名下 #

WITH duplicate_client_groups AS (
    SELECT 
        id,
        -- 确定同组的目标保留客户ID
        MIN(id) OVER (PARTITION BY name, business_id) AS target_client_id
    FROM clients
    WHERE EXISTS (
        SELECT 1 
        FROM clients c2
        WHERE c2.name = clients.name 
          AND c2.business_id = clients.business_id
          AND c2.id != clients.id
    )
)
UPDATE appointments a
SET client_id = dcg.target_client_id
FROM duplicate_client_groups dcg
WHERE a.client_id = dcg.id 
  AND dcg.id != dcg.target_client_id;

2. 删除待删除的重复客户 #

WITH duplicate_client_groups AS (
    SELECT 
        id,
        MIN(id) OVER (PARTITION BY name, business_id) AS target_client_id
    FROM clients
    WHERE EXISTS (
        SELECT 1 
        FROM clients c2
        WHERE c2.name = clients.name 
          AND c2.business_id = clients.business_id
          AND c2.id != clients.id
    )
)
DELETE FROM clients
WHERE id IN (
    SELECT id 
    FROM duplicate_client_groups 
    WHERE id != target_client_id
);

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