如何在MySQL中实现PHP考勤系统的动态透视表？

我正在开发基于PHP和MySQL的考勤系统，需要生成如下格式的学生考勤表：

我希望表中的科目为该班级开设的科目，若学生某科目无考勤记录，则状态显示为null。

我的数据库结构如下：

attendence: id, std_id, class_id, attendence_date, state -- 注：需补充state字段存储考勤状态（缺勤/迟到/出勤）
class:      id, name    
subjects:   id, name   
class_subjects: id, class_id, subject_id    
students:   id, name, class_id    

我尝试了以下代码，但未达到预期：

BEGIN
SET @sql=null;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN su.subject_name = ''',
      su.subject_name,
      ''' THEN subject_name ELSE NULL END) AS ',
      su.subject_name
    )
  ) INTO @sql
FROM class_subject cs JOIN subjects su on cs.subject_id=su.id WHERE cs.class_id = p_class;
SET @sql=concat('SELECT s.name, a.state ,',@sql, ' from attendence a join students s on a.std_id=s.id where a.attendence_date = ',p_date);
#SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END

首先确认你的attendence表需要补充state字段（用来存储"缺勤/迟到/出勤"状态），否则无法记录考勤结果。

修正后的动态SQL存储过程如下：

DELIMITER //
CREATE PROCEDURE GetStudentAttendance(IN p_class INT, IN p_date DATE)
BEGIN
    SET @sql = NULL;

    -- 生成动态科目列：每个科目对应一个CASE，获取学生当天该科目的考勤状态
    SELECT GROUP_CONCAT(DISTINCT
        CONCAT(
            'MAX(CASE WHEN cs.subject_id = su.id THEN a.state ELSE NULL END) AS `',
            su.name,
            '`'
        )
    ) INTO @sql
    FROM class_subjects cs
    JOIN subjects su ON cs.subject_id = su.id
    WHERE cs.class_id = p_class;

    -- 拼接完整SQL：LEFT JOIN确保所有班级学生都显示，哪怕无考勤记录
    SET @sql = CONCAT(
        'SELECT s.name AS `学生姓名`, ',
        @sql,
        ' FROM students s',
        ' LEFT JOIN attendence a ON s.id = a.std_id AND a.attendence_date = ? AND a.class_id = ?',
        ' LEFT JOIN class_subjects cs ON s.class_id = cs.class_id',
        ' LEFT JOIN subjects su ON cs.subject_id = su.id',
        ' WHERE s.class_id = ?',
        ' GROUP BY s.id, s.name'
    );

    -- 预编译并执行，用参数避免SQL注入
    PREPARE stmt FROM @sql;
    SET @class = p_class;
    SET @date = p_date;
    EXECUTE stmt USING @date, @class, @class;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

关键修正说明 #

• 动态列逻辑：CASE语句根据科目ID匹配考勤记录，返回对应的state状态值，而非科目名称，符合考勤表需求。
• LEFT JOIN关联：从students表出发关联考勤表，确保当天无考勤记录的学生也能被列出，对应科目状态自动为NULL。
• 参数化查询：用?作为占位符传递日期和班级参数，避免直接拼接字符串导致的SQL注入和语法错误。
• 分组处理：按学生ID和姓名分组，用MAX聚合函数确保每个学生仅显示一行，同时聚合对应科目的考勤状态。
• 表名修正：统一使用数据库结构中的class_subjects表名，修正原代码中的拼写错误。

使用示例：

CALL GetStudentAttendance(1, '2024-05-20');

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