多表关联统计公司代表人数结果异常问题排查

问题原因 #

直接用多表JOIN会产生笛卡尔积：比如某公司在sponsors有2条记录、mentors有3条，JOIN后会生成2×3=6条重复组合记录，COUNT(*)会把这些重复记录全部计入，导致结果远大于实际总人数。
另外原SQL存在两处语法问题：

• compnay_name是拼写错误，应为company_name
• 最后一个JOIN的customers和company未加表前缀，可能引发字段歧义

解决方案 #

方案一：子查询分别统计各表数量后求和

先单独统计每个关联表的公司记录数，再将结果相加，最后关联主表获取公司名称：

SELECT 
    c.company_name,
    COALESCE(s.sponsor_count, 0) +
    COALESCE(m.mentor_count, 0) +
    COALESCE(o.organizer_count, 0) +
    COALESCE(j.judge_count, 0) +
    COALESCE(cust.customer_count, 0) AS people_count
FROM hakaton.company c
LEFT JOIN (
    SELECT company_id, COUNT(*) AS sponsor_count
    FROM hakaton.sponsors
    GROUP BY company_id
) s ON c.company_id = s.company_id
LEFT JOIN (
    SELECT company_id, COUNT(*) AS mentor_count
    FROM hakaton.mentors
    GROUP BY company_id
) m ON c.company_id = m.company_id
LEFT JOIN (
    SELECT company_id, COUNT(*) AS organizer_count
    FROM hakaton.organizers
    GROUP BY company_id
) o ON c.company_id = o.company_id
LEFT JOIN (
    SELECT company_id, COUNT(*) AS judge_count
    FROM hakaton.judges
    GROUP BY company_id
) j ON c.company_id = j.company_id
LEFT JOIN (
    SELECT company_id, COUNT(*) AS customer_count
    FROM customers
    GROUP BY company_id
) cust ON c.company_id = cust.company_id
ORDER BY people_count DESC;

用LEFT JOIN+COALESCE是为了兼容部分公司在某关联表无记录的情况，逻辑更健壮。

方案二：用UNION ALL合并所有关联表后统计

把所有关联表的company_id合并到一个结果集，再分组统计总数，最后关联主表：

SELECT 
    c.company_name,
    cnt.total_count AS people_count
FROM hakaton.company c
JOIN (
    SELECT company_id, COUNT(*) AS total_count
    FROM (
        SELECT company_id FROM hakaton.sponsors
        UNION ALL
        SELECT company_id FROM hakaton.mentors
        UNION ALL
        SELECT company_id FROM hakaton.organizers
        UNION ALL
        SELECT company_id FROM hakaton.judges
        UNION ALL
        SELECT company_id FROM customers
    ) all_records
    GROUP BY company_id
) cnt ON c.company_id = cnt.company_id
ORDER BY people_count DESC;

UNION ALL不会去重，能完整保留所有原始记录，统计结果就是各表对应公司记录的真实总和，彻底避免笛卡尔积问题。

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