如何用count()结合子查询统计不同日期的注册码选课人数

核心思路 #

通过CTE（公共表表达式）拆分逻辑 + 条件聚合实现需求：

1. 先拆分出「每个用户对应的唯一注册码」和「用户选的指定日期课程」两个数据集
2. 以所有注册码为基础，左关联上述数据集，用条件聚合统计各日期的选课人数（确保返回所有注册码，无数据时显示0）

完整SQL查询 #

WITH user_reg_mapping AS (
    -- 提取每个用户对应的唯一注册码
    SELECT
        ae.account_id,
        e.event_id AS reg_code
    FROM added_events ae
    INNER JOIN events e 
        ON ae.event_id = e.event_id
    WHERE e.part_no = 'r'
),
user_selected_courses AS (
    -- 提取用户选的指定日期范围内的课程
    SELECT
        ae.account_id,
        e.event_day
    FROM added_events ae
    INNER JOIN events e 
        ON ae.event_id = e.event_id
    WHERE e.part_no = 'c'
      AND e.event_day IN ('2024/04/25', '2024/04/26', '2024/04/27')
)
SELECT
    r.event_id AS `Reg-code`,
    -- 统计周四（2024/04/25）的选课人数（去重用户）
    COUNT(DISTINCT CASE WHEN usc.event_day = '2024/04/25' THEN usc.account_id END) AS Num_att_Thu,
    -- 统计周五（2024/04/26）的选课人数
    COUNT(DISTINCT CASE WHEN usc.event_day = '2024/04/26' THEN usc.account_id END) AS Num_att_Fri,
    -- 统计周六（2024/04/27）的选课人数
    COUNT(DISTINCT CASE WHEN usc.event_day = '2024/04/27' THEN usc.account_id END) AS Num_att_Sat
FROM events r
-- 左关联确保所有注册码都被返回
LEFT JOIN user_reg_mapping urm 
    ON r.event_id = urm.reg_code
LEFT JOIN user_selected_courses usc 
    ON urm.account_id = usc.account_id
-- 只筛选注册码类型的活动
WHERE r.part_no = 'r'
GROUP BY r.event_id
ORDER BY r.event_id;

关键细节说明 #

1. CTE拆分逻辑：
   • user_reg_mapping：解决「每个用户对应唯一注册码」的关联问题，避免多表关联时的笛卡尔积
   • user_selected_courses：提前过滤出指定日期的课程，减少后续聚合计算的数据量
2. 条件聚合：
   • 使用CASE WHEN配合COUNT(DISTINCT)，将不同日期的统计结果转为列，同时去重用户（避免同一用户选多门同日期课程被重复统计）
   • 用LEFT JOIN保证即使注册码无选课用户，也会返回并显示0
3. 适配调整：
   • 如果注册码存储在events表的其他字段（如reg_code），替换r.event_id为对应字段即可
   • 若event_day格式为YYYY-MM-DD，需同步修改WHERE和CASE中的日期字符串
   • 若需统计课程数量而非用户数，去掉DISTINCT即可

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