如何用含UNION ALL子查询的SELECT语句统计各活动关联表条目总数？

嘿，你的思路其实已经找对方向啦！用UNION ALL来合并多表计数是个很合适的方案，我帮你把SQL补全并优化了两种写法，都能一次性拿到每个活动对应的三张表条目数，以及它们的总和：

写法一：子查询分别统计后关联 #

这种写法逻辑清晰，每个表的计数独立计算，再和活动表关联：

SELECT 
    a.id AS activityID,
    a.name,
    -- 用COALESCE把NULL转为0，避免无数据时显示空值
    COALESCE(q.quiz_count, 0) AS quiz_count,
    COALESCE(c.comment_count, 0) AS comment_count,
    COALESCE(qt.question_count, 0) AS question_count,
    -- 直接求和得到总数
    COALESCE(q.quiz_count, 0) + COALESCE(c.comment_count, 0) + COALESCE(qt.question_count, 0) AS total_count
FROM 
    activity a
-- 左连接保证即使活动在某表无数据也能保留
LEFT JOIN (
    SELECT activityID, COUNT(*) AS quiz_count FROM quiz GROUP BY activityID
) q ON a.id = q.activityID
LEFT JOIN (
    SELECT activityID, COUNT(*) AS comment_count FROM comment GROUP BY activityID
) c ON a.id = c.activityID
LEFT JOIN (
    SELECT activityID, COUNT(*) AS question_count FROM question GROUP BY activityID
) qt ON a.id = qt.activityID;

写法二：先合并多表计数再聚合 #

这种写法先把三个表的计数结果合并成一个数据集，再分组统计各表数量：

SELECT 
    a.id AS activityID,
    a.name,
    -- 按来源表分组求和
    SUM(CASE WHEN source = 'quiz' THEN count ELSE 0 END) AS quiz_count,
    SUM(CASE WHEN source = 'comment' THEN count ELSE 0 END) AS comment_count,
    SUM(CASE WHEN source = 'question' THEN count ELSE 0 END) AS question_count,
    -- 直接求和所有计数得到总数
    SUM(count) AS total_count
FROM 
    activity a
LEFT JOIN (
    -- 给每个表的计数标记来源
    SELECT activityID, COUNT(*) AS count, 'quiz' AS source FROM quiz GROUP BY activityID
    UNION ALL
    SELECT activityID, COUNT(*) AS count, 'comment' AS source FROM comment GROUP BY activityID
    UNION ALL
    SELECT activityID, COUNT(*) AS count, 'question' AS source FROM question GROUP BY activityID
) t ON a.id = t.activityID
GROUP BY a.id, a.name;

小提示： #

• 用LEFT JOIN是为了保留所有活动，哪怕某个活动在三张表里都没有数据，对应的计数会显示0；如果只需要统计有数据的活动，换成INNER JOIN就行。
• COALESCE函数的作用是把NULL值转换成0，避免求和时出现NULL结果。

内容的提问来源于stack exchange，提问作者Maurício Giordano