如何在数据库attendance_tbl中排除节假日的考勤/缺勤/迟到统计

看起来你已经搭好了基础的考勤统计逻辑，要实现排除节假日的需求，核心就是在查询中过滤掉考勤日期属于节假日的记录。下面是具体的实现方案，结合你的现有代码调整：

前提假设 #

我先做几个合理假设（如果和你的实际表结构不符，替换成对应字段/表名即可）：

• attendance_tbl 中有记录考勤日期的字段 attendance_date（DATE类型）
• 你有一个专门存储节假日的表 holidays_tbl，其中包含字段 holiday_date（存储所有节假日的DATE值）

修改后的完整查询代码 #

SELECT 
    s.student_id as 'Student_Number',
    CONCAT(IFNULL(s.student_fname,''),' ', IFNULL(s.student_mname,''),' ', IFNULL(s.student_lname,'')) 'Student_Name',
    s.student_program as 'Program',
    SUM(IF(stat.status_description='Late',1,0)) 'Total_Lates',
    SUM(IF(stat.status_description='Absent',1,0)) 'Total_Absences',
    FLOOR((SUM(IF(stat.status_description='Late',1,0)) / 3) + SUM(IF(stat.status_description='Absent',1,0))) 'Total_Absence_with_Lates'
FROM attendance_tbl a
LEFT JOIN students_tbl s ON a.student_id = s.student_id  -- 补全你原本的学生表关联逻辑
LEFT JOIN status_tbl stat ON a.status_id = stat.status_id  -- 补全你原本的状态表关联逻辑
-- 关键过滤条件：排除所有节假日的考勤记录
WHERE a.attendance_date NOT IN (SELECT holiday_date FROM holidays_tbl)
-- 可选：如果还要排除周末（周六周日），添加下面这行
-- AND DAYOFWEEK(a.attendance_date) NOT IN (1,7)  -- MySQL中1=周日，7=周六，其他数据库需调整函数
GROUP BY s.student_id, s.student_fname, s.student_mname, s.student_lname, s.student_program;

关键细节说明 #

1. 没有专门的节假日表怎么办？
   如果暂时没建节假日表，可以直接枚举节假日日期来过滤，比如：

   WHERE a.attendance_date NOT IN ('2024-01-01', '2024-02-10', '2024-04-04', '2024-05-01')
   

   但这种方式维护成本高，后续节假日变动需要手动修改SQL，还是推荐建一个holidays_tbl来统一管理。

2. 数据库星期函数差异
   如果要排除周末，不同数据库的星期判断函数不一样：

   • MySQL：DAYOFWEEK(date)（1=周日，7=周六）
   • SQL Server：DATEPART(dw, date)（1=周日，7=周六，取决于服务器设置）
   • PostgreSQL：EXTRACT(DOW FROM date)（0=周日，6=周六）

3. 避免NULL日期干扰
   如果attendance_date可能存在NULL值，建议在WHERE条件中加上AND a.attendance_date IS NOT NULL，防止无效数据影响统计结果。

4. GROUP BY兼容性
   确保GROUP BY子句包含所有非聚合字段（也就是SELECT里除了SUM、FLOOR这些聚合函数之外的字段），否则在开启严格模式的数据库（比如MySQL的ONLY_FULL_GROUP_BY）中会报错。

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