SQL按月统计学生出勤：如何同时计算缺勤天数与月度总上课天数

我来帮你修正SQL语句，解决同时统计学生缺勤天数和对应月份总上课天数的问题。原SQL的核心问题是通过WHERE a.ASISTENCIA = 0过滤了出勤记录，导致无法计算月度总上课天数，我们需要调整逻辑来同时获取这两个指标。

方法1：使用CTE（通用表表达式）清晰拆分逻辑 #

这种方法先单独计算每个月的总上课天数，再关联到学生的出勤统计，可读性更强：

BEGIN
WITH monthly_class_days AS (
    -- 先计算指定年级、年份下每个月的总上课天数（去重日期数）
    SELECT 
        MONTH(FECHA_ASISTENCIA) AS month,
        COUNT(DISTINCT FECHA_ASISTENCIA) AS total_class_days
    FROM asistencia
    WHERE 
        EDUCATION_LEVEL_ID = @EDUCATION_LEVEL_ID
        AND YEAR(FECHA_ASISTENCIA) = @EDUCATION_LEVEL_YEAR
    GROUP BY MONTH(FECHA_ASISTENCIA)
)
SELECT 
    u.user_id,
    u.user_first_name AS names,
    u.user_last_name_01 AS lastname1,
    u.user_last_name_02 AS lastname2,
    MONTH(a.FECHA_ASISTENCIA) AS month,
    -- 统计该学生当月缺勤天数（ASISTENCIA=0的记录数）
    SUM(CASE WHEN a.ASISTENCIA = 0 THEN 1 ELSE 0 END) AS absent_days,
    -- 关联CTE获取月度总上课天数
    mcd.total_class_days,
    p.CITY AS city
FROM users u
INNER JOIN asistencia a ON u.user_id = a.USER_ID
INNER JOIN profile p ON u.rut_SF = p.RUT_SF
INNER JOIN monthly_class_days mcd ON MONTH(a.FECHA_ASISTENCIA) = mcd.month
WHERE 
    a.EDUCATION_LEVEL_ID = @EDUCATION_LEVEL_ID
    AND YEAR(a.FECHA_ASISTENCIA) = @EDUCATION_LEVEL_YEAR
GROUP BY 
    u.user_id,
    u.user_first_name,
    u.user_last_name_01,
    u.user_last_name_02,
    MONTH(a.FECHA_ASISTENCIA),
    p.CITY,
    mcd.total_class_days
ORDER BY month;
END

方法2：使用相关子查询（适合不支持CTE的数据库） #

如果你的数据库版本不支持CTE（比如MySQL 5.x及以下），可以用相关子查询直接计算月度总上课天数：

BEGIN
SELECT 
    u.user_id,
    u.user_first_name AS names,
    u.user_last_name_01 AS lastname1,
    u.user_last_name_02 AS lastname2,
    MONTH(a.FECHA_ASISTENCIA) AS month,
    SUM(CASE WHEN a.ASISTENCIA = 0 THEN 1 ELSE 0 END) AS absent_days,
    -- 子查询计算当前月份的总上课天数
    (SELECT COUNT(DISTINCT FECHA_ASISTENCIA) 
     FROM asistencia 
     WHERE EDUCATION_LEVEL_ID = @EDUCATION_LEVEL_ID
       AND YEAR(FECHA_ASISTENCIA) = @EDUCATION_LEVEL_YEAR
       AND MONTH(FECHA_ASISTENCIA) = MONTH(a.FECHA_ASISTENCIA)) AS total_class_days,
    p.CITY AS city
FROM users u
INNER JOIN asistencia a ON u.user_id = a.USER_ID
INNER JOIN profile p ON u.rut_SF = p.RUT_SF
WHERE 
    a.EDUCATION_LEVEL_ID = @EDUCATION_LEVEL_ID
    AND YEAR(a.FECHA_ASISTENCIA) = @EDUCATION_LEVEL_YEAR
GROUP BY 
    u.user_id,
    u.user_first_name,
    u.user_last_name_01,
    u.user_last_name_02,
    MONTH(a.FECHA_ASISTENCIA),
    p.CITY
ORDER BY month;
END

关键逻辑说明 #

1. 移除ASISTENCIA=0的过滤条件：这样能保留所有出勤/缺勤记录，既可以统计缺勤天数，也能用于关联计算月度总上课天数。
2. 用SUM(CASE...)统计缺勤天数：替代原有的COUNT(*)，精准统计ASISTENCIA=0的记录数，避免过滤数据带来的误差。
3. 月度总上课天数的计算：通过COUNT(DISTINCT FECHA_ASISTENCIA)获取指定年级当月的上课日总数（去重日期，因为每天每个学生对应一条记录），确保同一个月份的所有学生拿到相同的总上课天数。

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