如何将outreach表数值累加至students表count()分组统计结果？

需求背景 #

现有两张业务表：

• students表：存储学生与对应班级/年级的关联信息
• outreach表：存储各活动及对应班级/年级的参与学生数

需要统计各班级/年级的触达学生总数，计算逻辑为「班级总人数 + 该班级所有活动的参与人数之和」（比如1班总人数45，两个活动参与人数36和40，最终结果为45+36+40=121），要求避免循环或拼接查询语句，用更优雅的SQL写法实现。

解决方案 #

可以通过子查询分组聚合+关联求和的方式实现，核心思路是先分别统计两张表的聚合数据，再关联后计算总和：

SELECT 
    -- 确保班级ID不为空，兼容仅存在学生或仅存在活动数据的班级
    COALESCE(s.class_id, o.class_id) AS class_id,
    -- 处理NULL值，避免求和时出现异常结果
    COALESCE(s.total_students, 0) + COALESCE(o.total_outreach_participants, 0) AS total_reach
FROM (
    -- 从students表统计各班级总人数
    SELECT class_id, COUNT(*) AS total_students
    FROM students
    GROUP BY class_id
) s
-- 全外连接确保所有有数据的班级都被统计到
FULL OUTER JOIN (
    -- 从outreach表统计各班级所有活动的参与人数总和
    SELECT class_id, SUM(participants) AS total_outreach_participants
    FROM outreach
    GROUP BY class_id
) o ON s.class_id = o.class_id
ORDER BY class_id;

关键细节说明 #

• FULL OUTER JOIN的作用：兼容三种场景：有学生数据且有活动数据的班级、只有学生数据无活动的班级、只有活动数据无学生的班级
• COALESCE函数：将聚合结果中的NULL值转为0，避免因某张表无对应班级数据导致求和结果为NULL
• 灵活性调整：如果只需要统计有学生的班级，可将FULL OUTER JOIN替换为LEFT JOIN，以students的聚合结果为主表

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