MySQL存储过程统计指定班级科目下学生考勤数据的实现方法

表字段前置说明 #

默认AttendanceLog表核心字段如下，和你实际表结构不一致的话对应替换字段名即可：

• keaclass_id：班级唯一ID，整型
• subject_id：科目唯一ID，整型
• username_fk：学生关联用户名，字符串类型
• attendance_date：考勤对应开课日期，日期/时间戳类型
• is_attended：出勤状态标记，1=正常出勤，0=缺勤/未签到

注：如果你的表规则是只要存在学生对应考勤记录就算出勤，无需额外判断状态，后续统计逻辑可以直接去掉is_attended的判断条件。

MySQL版存储过程代码 #

DELIMITER //
CREATE PROCEDURE GetClassSubjectAttendance(IN p_keaclass_id INT, IN p_subject_id INT)
BEGIN
    -- 统计指定班级指定科目的总开课次数
    DECLARE total_course_count INT DEFAULT 0;
    SELECT COUNT(DISTINCT attendance_date) INTO total_course_count
    FROM AttendanceLog
    WHERE keaclass_id = p_keaclass_id 
      AND subject_id = p_subject_id;
    
    -- 返回每个学生的考勤统计结果
    SELECT 
        username_fk AS 学生用户名,
        SUM(CASE WHEN is_attended = 1 THEN 1 ELSE 0 END) AS 实际出勤次数,
        total_course_count AS 总开课次数,
        CONCAT(ROUND(SUM(CASE WHEN is_attended = 1 THEN 1 ELSE 0 END) / total_course_count * 100, 2), '%') AS 出勤占比
    FROM AttendanceLog
    WHERE keaclass_id = p_keaclass_id 
      AND subject_id = p_subject_id
    GROUP BY username_fk
    ORDER BY 出勤占比 DESC;
END //
DELIMITER ;

调用方法 #

直接传入你要查询的班级ID和科目ID即可：
CALL GetClassSubjectAttendance(要查询的班级ID, 要查询的科目ID);
比如你举的例子里subject_id为2，对应班级ID为5的话，调用CALL GetClassSubjectAttendance(5, 2);，返回结果就会和你要求的格式完全一致：总开课次数为4，学生Nadi6548出勤4次占比100%，学生nico4108出勤1次占比25%。

其他数据库适配（PostgreSQL为例） #

CREATE OR REPLACE PROCEDURE GetClassSubjectAttendance(p_keaclass_id INT, p_subject_id INT)
LANGUAGE plpgsql
AS $$
DECLARE
    total_course_count INT;
BEGIN
    SELECT COUNT(DISTINCT attendance_date) INTO total_course_count
    FROM AttendanceLog
    WHERE keaclass_id = p_keaclass_id AND subject_id = p_subject_id;
    
    RETURN QUERY
    SELECT 
        username_fk AS 学生用户名,
        SUM(CASE WHEN is_attended = 1 THEN 1 ELSE 0 END) AS 实际出勤次数,
        total_course_count AS 总开课次数,
        ROUND(SUM(CASE WHEN is_attended = 1 THEN 1 ELSE 0 END)::numeric / total_course_count * 100, 2) || '%' AS 出勤占比
    FROM AttendanceLog
    WHERE keaclass_id = p_keaclass_id AND subject_id = p_subject_id
    GROUP BY username_fk
    ORDER BY 出勤占比 DESC;
END;
$$;

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