MySQL指纹考勤日志按排班汇总：实现员工每日考勤单行对齐

核心问题分析 #

原SQL的错误在于内层按empno, punch_datetime分组，导致每条打卡记录单独成一行，完全没实现按日期聚合；同时未处理跨天排班的考勤日期映射，导致不同排班的打卡无法归到正确的日期行。

解决方案步骤 #

1. 映射打卡时间到考勤日期：针对跨天排班（如00:00-8:00），将凌晨0-8点的打卡归为前一天的考勤日期，其他时段归为当天日期。
2. 按员工+考勤日期分组聚合：对每个员工的每日考勤，分别取各打卡类型的最早/最晚时间（上班取最早，下班取最晚）。

最终SQL语句 #

SELECT
    empno,
    att_date,
    MIN(CASE WHEN punch_code = '0' THEN punch_datetime END) AS am_in,
    MAX(CASE WHEN punch_code = '1' THEN punch_datetime END) AS am_out,
    MIN(CASE WHEN punch_code = '2' THEN punch_datetime END) AS pm_in,
    MAX(CASE WHEN punch_code = '3' THEN punch_datetime END) AS pm_out,
    MIN(CASE WHEN punch_code = '4' THEN punch_datetime END) AS ov_in,
    MAX(CASE WHEN punch_code = '5' THEN punch_datetime END) AS ov_out
FROM (
    SELECT
        empno,
        punch_code,
        punch_datetime,
        -- 处理跨天考勤日期：00:00-08:00的打卡归为前一天，其余归当天
        DATE(
            CASE
                WHEN TIME(punch_datetime) < '08:00:00' THEN DATE_SUB(punch_datetime, INTERVAL 1 DAY)
                ELSE punch_datetime
            END
        ) AS att_date
    FROM biometric_log
    WHERE punch_code IN ('0', '1', '2', '3', '4', '5')
) t
GROUP BY empno, att_date
ORDER BY empno, att_date;

适配不同排班的优化说明 #

如果有员工固定排班配置（需额外维护emp_schedule表，包含empno, shift_start, shift_end字段），可以更精准地映射考勤日期：

-- 基于员工排班表的精准日期映射
SELECT
    t.empno,
    t.att_date,
    MIN(CASE WHEN t.punch_code = '0' THEN t.punch_datetime END) AS am_in,
    MAX(CASE WHEN t.punch_code = '1' THEN t.punch_datetime END) AS am_out,
    MIN(CASE WHEN t.punch_code = '2' THEN t.punch_datetime END) AS pm_in,
    MAX(CASE WHEN t.punch_code = '3' THEN t.punch_datetime END) AS pm_out,
    MIN(CASE WHEN t.punch_code = '4' THEN t.punch_datetime END) AS ov_in,
    MAX(CASE WHEN t.punch_code = '5' THEN t.punch_datetime END) AS ov_out
FROM (
    SELECT
        b.empno,
        b.punch_code,
        b.punch_datetime,
        -- 根据员工排班判断考勤日期：打卡时间早于排班起始时间则归为前一天
        DATE(
            CASE
                WHEN TIME(b.punch_datetime) < s.shift_start THEN DATE_SUB(b.punch_datetime, INTERVAL 1 DAY)
                ELSE b.punch_datetime
            END
        ) AS att_date
    FROM biometric_log b
    JOIN emp_schedule s ON b.empno = s.empno
    WHERE b.punch_code IN ('0', '1', '2', '3', '4', '5')
) t
GROUP BY t.empno, t.att_date
ORDER BY t.empno, t.att_date;

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