多表LEFT OUTER JOIN统计异常：讨论数与消息数为何相等？

你遇到的问题核心是两次左连接产生了笛卡尔积：

• 当某条讨论下关联多条消息时，这条讨论记录会和每条消息逐一配对，导致它在连接后的结果集中重复出现（重复次数等于该讨论的消息数）。
• COUNT("Discussions"."id") 统计的是结果集中非空的Discussions.id数量，由于每条消息都对应一条有效的讨论记录，这个计数就等于消息的总数量，自然和COUNT("Discussions->Messages"."id")的结果完全一致。

有两种实用的修正方案：

方案一：用DISTINCT去重统计讨论数 #

在统计讨论数时，通过DISTINCT关键字避免重复计数：

SELECT 
    "Forum"."id", "Forum"."label", "Forum"."name",    
    COUNT(DISTINCT "Discussions"."id") AS "nbDiscussions", 
    COUNT("Discussions->Messages"."id") AS "nbMessages"
FROM 
    "forums" AS "Forum"
LEFT OUTER JOIN 
    "discussions" AS "Discussions" ON "Forum"."id" = "Discussions"."forum_id"
LEFT OUTER JOIN 
    "messages" AS "Discussions->Messages" ON "Discussions"."id" = "Discussions->Messages"."discussion_id"
GROUP BY 
    "Forum"."id"
ORDER BY 
    "Forum"."id";

方案二：子查询预先统计（更高效） #

先分别统计每个论坛的讨论数和消息数，再和论坛表关联，避免大表之间的笛卡尔积：

SELECT 
    f.id, f.label, f.name,
    COALESCE(d.nbDiscussions, 0) AS nbDiscussions,
    COALESCE(m.nbMessages, 0) AS nbMessages
FROM forums f
LEFT JOIN (
    SELECT forum_id, COUNT(id) AS nbDiscussions
    FROM discussions
    GROUP BY forum_id
) d ON f.id = d.forum_id
LEFT JOIN (
    SELECT 
        d.forum_id, COUNT(m.id) AS nbMessages
    FROM discussions d
    JOIN messages m ON d.id = m.discussion_id
    GROUP BY d.forum_id
) m ON f.id = m.forum_id
ORDER BY f.id;

这里用COALESCE是为了让没有讨论/消息的论坛显示0，而不是默认的NULL。

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