如何高效合并多表关联查询的SQL统计计数结果？

问题背景 #

需要统计每个concept对应的questions和cards数量，目前通过UNION ALL加二次GROUP BY能得到正确结果，但步骤繁琐；尝试关联子查询的方式却得到错误计数。

正确但繁琐的实现方式 #

代码 #

SELECT
  x.id,
  sum(x.question_count) AS question_count,
  sum(x.card_count) AS card_count
FROM (
  SELECT
    c.id,
    count(*) AS question_count,
    0 AS card_count
  FROM
    concepts AS c
    INNER JOIN questions ON c.id = questions."conceptId"
  GROUP BY
    c.id
  UNION ALL
  SELECT
    c.id,
    0 AS question_count,
    count(*) AS card_count
  FROM
    concepts AS c
    INNER JOIN cards ON c.id = cards."conceptId"
  GROUP BY
    c.id) AS x
GROUP BY
  x.id
ORDER BY x.id;

正确输出 #

错误的关联子查询实现 #

代码 #

SELECT
  x."conceptId",
  q_count,
  c_count
FROM (
  SELECT
    q."conceptId",
    count(*) AS q_count
  FROM
    questions AS q
  GROUP BY
    q."conceptId") AS x
  INNER JOIN (
    SELECT
      c."conceptId",
      count(*) AS c_count
    FROM
      questions AS c  -- 此处笔误：应查询cards表而非questions表
    GROUP BY
      c."conceptId") AS y ON x."conceptId" = y."conceptId";

错误输出 #

问题分析与优化方案 #

错误代码的直接原因是统计c_count时误将cards表写成了questions表；即使修正表名，INNER JOIN也会过滤掉仅关联单表的concept记录，导致结果不完整。以下是两种更高效的优化方案：

方式一：LEFT JOIN + 子查询分组统计 #

从concepts表出发，左连接两个分组后的子查询，确保所有concept都被统计：

SELECT
  c.id,
  COALESCE(q.q_count, 0) AS question_count,
  COALESCE(cd.c_count, 0) AS card_count
FROM concepts c
LEFT JOIN (
  SELECT "conceptId", COUNT(*) AS q_count
  FROM questions
  GROUP BY "conceptId"
) q ON c.id = q."conceptId"
LEFT JOIN (
  SELECT "conceptId", COUNT(*) AS c_count
  FROM cards
  GROUP BY "conceptId"
) cd ON c.id = cd."conceptId"
ORDER BY c.id;

• LEFT JOIN保留所有concept记录，避免丢失仅关联单表的数据
• COALESCE将NULL值转为0，保证计数结果格式统一

方式二：窗口函数（适用于PostgreSQL、MySQL 8+等支持窗口函数的数据库） #

利用窗口函数直接统计，避免嵌套子查询：

SELECT DISTINCT
  c.id,
  COUNT(q.id) OVER (PARTITION BY c.id) AS question_count,
  COUNT(cd.id) OVER (PARTITION BY c.id) AS card_count
FROM concepts c
LEFT JOIN questions q ON c.id = q."conceptId"
LEFT JOIN cards cd ON c.id = cd."conceptId"
ORDER BY c.id;

• DISTINCT去除关联产生的笛卡尔积重复记录
• 窗口函数按concept.id分组计数，直接得到每个concept的对应总数量

以上两种方案均比原UNION ALL+二次GROUP BY写法更简洁高效，同时保证结果准确。

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