MySQL多表关联统计问题：查询用户及名下各类物品数量

问题场景 #

需要从persons表查询所有用户信息，同时统计每位用户在pens、chairs、books表中拥有的物品数量。现有数据如下：

-- persons表数据
select * from persons;
+----+-------+
| id | name  |
+----+-------+
|  1 | Alex  |
|  2 | Brad  |
|  3 | Cathy |
+----+-------+

-- pens表数据
select * from pens;
+----+-----------+
| id | person_id |
+----+-----------+
|  1 | 2         |
|  2 | 2         |
|  3 | 2         |
|  4 | 3         |
+----+-----------+

-- chairs表数据
select * from chairs;
+----+-----------+
| id | person_id |
+----+-----------+
|  1 | 1         |
+----+-----------+

-- books表数据
select * from books;
+----+-----------+
| id | person_id |
+----+-----------+
|  1 | 1         |
|  2 | 2         |
|  3 | 3         |
+----+-----------+

期望结果：

+----+-------+------------+--------------+-------------+
| id | name  | count_pens | count_chairs | count_books |
+----+-------+------------+--------------+-------------+
|  1 | Alex  |          0 |            1 |           1 |
|  2 | Brad  |          3 |            0 |           1 |
|  3 | Cathy |          1 |            0 |           1 |
+----+-------+------------+--------------+-------------+

错误原因分析 #

使用普通LEFT JOIN后统计结果异常（如Brad的count_books显示为3而非1），是因为多表左连接会产生笛卡尔积：当用户在某张表中有多条记录时，会和其他表的记录进行组合，导致重复计数。比如Brad在pens有3条记录，books有1条记录，连接后会生成3条重复的books记录，直接count(books.person_id)会统计到3次，而非实际的1次。

解决方案 #

方案1：子查询预统计（推荐，高效）

先对每个物品表单独分组统计用户的物品数量，再与persons表左连接，避免笛卡尔积问题：

SELECT
    p.id,
    p.name,
    COALESCE(pen_stats.pen_count, 0) AS count_pens,
    COALESCE(chair_stats.chair_count, 0) AS count_chairs,
    COALESCE(book_stats.book_count, 0) AS count_books
FROM persons p
LEFT JOIN (
    SELECT person_id, COUNT(*) AS pen_count
    FROM pens
    GROUP BY person_id
) pen_stats ON pen_stats.person_id = p.id
LEFT JOIN (
    SELECT person_id, COUNT(*) AS chair_count
    FROM chairs
    GROUP BY person_id
) chair_stats ON chair_stats.person_id = p.id
LEFT JOIN (
    SELECT person_id, COUNT(*) AS book_count
    FROM books
    GROUP BY person_id
) book_stats ON book_stats.person_id = p.id;

• COALESCE函数用于将NULL（无对应物品的用户）转换为0，符合期望结果格式。
• 子查询先完成分组统计，减少了后续连接的数据量，性能更优。

方案2：使用COUNT(DISTINCT)

通过统计各物品表的主键（唯一标识）去重，避免重复计数：

SELECT
    p.id,
    p.name,
    COUNT(DISTINCT pens.id) AS count_pens,
    COUNT(DISTINCT chairs.id) AS count_chairs,
    COUNT(DISTINCT books.id) AS count_books
FROM persons p
LEFT JOIN pens ON pens.person_id = p.id
LEFT JOIN chairs ON chairs.person_id = p.id
LEFT JOIN books ON books.person_id = p.id
GROUP BY p.id, p.name;

• 必须统计各表的主键（如pens.id）而非person_id，因为person_id在多条记录中重复，无法通过它去重。
• 写法更简洁，但数据量较大时，DISTINCT操作可能会增加性能开销。

结果验证 #

两种方案均可得到符合预期的统计结果，解决了原查询中重复计数的问题。

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