MySQL GROUP BY 实现学科选课、讲座、辅导课注册人数统计问题咨询

原有语句问题点 #

• GROUP BY逻辑错误：需求是按每个学科维度统计，原有语句group by r.subject_id, r.session会将同一个学科的LEC、TUT场次拆分为不同分组，最终输出是单学科单场次类型的统计结果，不符合一行展示一个学科全部统计项的要求。
• 条件计数用法错误：count(r.session LIKE 'LEC')不会按预期统计符合LEC条件的行数：COUNT函数只会忽略NULL值，而r.session LIKE 'LEC'返回的是布尔值TRUE/FALSE，二者都非空，所以最终统计的是全部行数，无法区分场次类型。
• 关联条件冗余错误：关联条件额外加了r.session = s.session，会过滤掉学生报名的session和subject表配置的session不匹配的记录，导致统计数据缺失。
• 总人数统计未去重（可选优化）：如果同一个学生同一学科报名了多场不同类型的课程，count(stud_id)会重复统计该学生，如需统计实际的学生人数，需要加上DISTINCT去重。

修正后的查询语句 #

基础版本（仅统计有报名记录的学科） #

SELECT 
    s.subject_id,
    s.subject_name,
    COUNT(DISTINCT r.stud_id) AS 总注册学生人数,
    SUM(CASE WHEN r.session = 'LEC' THEN 1 ELSE 0 END) AS 报名LEC讲座人次,
    SUM(CASE WHEN r.session = 'TUT' THEN 1 ELSE 0 END) AS 报名TUT辅导课人次
FROM subject s
INNER JOIN registration r ON s.subject_id = r.subject_id
GROUP BY s.subject_id, s.subject_name;

全量版本（展示所有学科，无人报名的学科人数显示为0） #

SELECT 
    s.subject_id,
    s.subject_name,
    COUNT(DISTINCT r.stud_id) AS 总注册学生人数,
    IFNULL(SUM(CASE WHEN r.session = 'LEC' THEN 1 ELSE 0 END), 0) AS 报名LEC讲座人次,
    IFNULL(SUM(CASE WHEN r.session = 'TUT' THEN 1 ELSE 0 END), 0) AS 报名TUT辅导课人次
FROM subject s
LEFT JOIN registration r ON s.subject_id = r.subject_id
GROUP BY s.subject_id, s.subject_name;

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