基于月份范围列展示考勤数据的MySQL查询/存储过程需求

一、固定月份范围的静态SQL查询 #

如果你的月份范围是固定的（比如示例中的2023年9-11月），可以直接通过多条件聚合实现按月度拆分列：

SELECT 
    class AS 'Class',
    -- 统计9月出勤/缺勤
    SUM(IF(DATE_FORMAT(attendance_date, '%b-%y') = 'Sep-23' AND Attendance = 'Present', 1, 0)) AS 'Sep-23 Present',
    SUM(IF(DATE_FORMAT(attendance_date, '%b-%y') = 'Sep-23' AND Attendance = 'Absent', 1, 0)) AS 'Sep-23 Absent',
    -- 统计10月出勤/缺勤
    SUM(IF(DATE_FORMAT(attendance_date, '%b-%y') = 'Oct-23' AND Attendance = 'Present', 1, 0)) AS 'Oct-23 Present',
    SUM(IF(DATE_FORMAT(attendance_date, '%b-%y') = 'Oct-23' AND Attendance = 'Absent', 1, 0)) AS 'Oct-23 Absent',
    -- 统计11月出勤/缺勤
    SUM(IF(DATE_FORMAT(attendance_date, '%b-%y') = 'Nov-23' AND Attendance = 'Present', 1, 0)) AS 'Nov-23 Present',
    SUM(IF(DATE_FORMAT(attendance_date, '%b-%y') = 'Nov-23' AND Attendance = 'Absent', 1, 0)) AS 'Nov-23 Absent'
FROM attendance_data
WHERE attendance_date BETWEEN '2023-09-01' AND '2023-11-30' -- 注意用月末日期，避免遗漏数据
GROUP BY class;

关键说明： #

• DATE_FORMAT(attendance_date, '%b-%y') 将日期格式化为Jan-23样式的缩写月份+年份，匹配需求的列名格式。
• 每个月份对应两组SUM(IF(...))，分别过滤该月的出勤、缺勤记录并统计数量。
• 原查询的问题是未按月份拆分统计条件，仅汇总了全时间段的总数，此方法通过精准的月份判断实现了拆分。

二、动态月份范围的存储过程 #

如果需要支持任意可选的月份范围（非固定），可以用存储过程生成动态SQL，自动适配时间范围：

DELIMITER //

CREATE PROCEDURE GetMonthlyAttendance(IN start_date DATE, IN end_date DATE)
BEGIN
    -- 创建临时表存储时间范围内的月份标签
    CREATE TEMPORARY TABLE IF NOT EXISTS months_list (
        month_label VARCHAR(7)
    );
    TRUNCATE TABLE months_list;

    -- 填充时间范围内的所有月份
    SET @current_month = start_date;
    WHILE @current_month <= end_date DO
        INSERT INTO months_list VALUES (DATE_FORMAT(@current_month, '%b-%y'));
        SET @current_month = DATE_ADD(@current_month, INTERVAL 1 MONTH);
    END WHILE;

    -- 动态拼接SQL列语句
    SET @sql = 'SELECT class AS ''Class''';
    SELECT GROUP_CONCAT(
        CONCAT(
            ', SUM(IF(DATE_FORMAT(attendance_date, ''%b-%y'') = ''', month_label, ''' AND Attendance = ''Present'', 1, 0)) AS ''', month_label, ' Present''',
            ', SUM(IF(DATE_FORMAT(attendance_date, ''%b-%y'') = ''', month_label, ''' AND Attendance = ''Absent'', 1, 0)) AS ''', month_label, ' Absent'''
        )
    ) INTO @columns FROM months_list;

    -- 拼接完整SQL并执行
    SET @sql = CONCAT(@sql, @columns, ' FROM attendance_data WHERE attendance_date BETWEEN ''', start_date, ''' AND ''', end_date, ''' GROUP BY class');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- 清理临时表
    DROP TEMPORARY TABLE IF EXISTS months_list;
END //

DELIMITER ;

调用示例： #

-- 查询2023年1月-3月的月度出勤数据
CALL GetMonthlyAttendance('2023-01-01', '2023-03-31');

此存储过程会自动根据输入的起止日期，生成对应每个月份的出勤、缺勤列，无需手动修改SQL语句。

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