MySQL多表关联统计异常：如何正确统计每人的颜色与水果数量？

你当前的SQL统计数值不准，核心原因是多表LEFT JOIN产生了笛卡尔积。比如某个人在TABLE_COLOR有3条记录、TABLE_FRUIT有2条记录，JOIN后会生成3×2=6条重复关联的记录，这时候COUNT出来的数值会被放大，完全偏离实际的记录数。

下面给两种靠谱的解决思路：

方法一：子查询预先统计各表数据 #

先分别在TABLE_COLOR和TABLE_FRUIT里按people_id统计好数量，再和TABLE_PEOPLE关联，从根源避免笛卡尔积：

SELECT
    p.name AS name,
    COALESCE(c.count_colors, 0) AS count_colors,
    COALESCE(f.count_fruits, 0) AS count_fruits
FROM TABLE_PEOPLE p
LEFT JOIN (
    SELECT people_id, COUNT(*) AS count_colors
    FROM TABLE_COLOR
    GROUP BY people_id
) c ON c.people_id = p.id
LEFT JOIN (
    SELECT people_id, COUNT(*) AS count_fruits
    FROM TABLE_FRUIT
    GROUP BY people_id
) f ON f.people_id = p.id
ORDER BY p.id;

用COALESCE是为了处理那些没有颜色或水果记录的人员，让结果返回0而不是NULL。

方法二：COUNT时加DISTINCT去重 #

如果不想改JOIN的结构，可以在COUNT里加上DISTINCT，统计唯一的记录标识（假设TABLE_COLOR有主键color_id，TABLE_FRUIT有主键fruit_id）：

SELECT
    TABLE_PEOPLE.name AS name,
    COUNT(DISTINCT TABLE_COLOR.color_id) AS count_colors,
    COUNT(DISTINCT TABLE_FRUIT.fruit_id) AS count_fruits
FROM TABLE_PEOPLE
LEFT JOIN TABLE_COLOR ON TABLE_COLOR.people_id = TABLE_PEOPLE.id
LEFT JOIN TABLE_FRUIT ON TABLE_FRUIT.people_id = TABLE_PEOPLE.id
GROUP BY TABLE_PEOPLE.id
ORDER BY TABLE_PEOPLE.id;

要是表没有主键，也可以用能唯一标识每条记录的字段（比如TABLE_COLOR的color字段如果每条记录唯一，也可以用COUNT(DISTINCT TABLE_COLOR.color)），但用主键是最稳妥的方式。

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