MySQL多班次员工考勤查询优化：修正夜班打卡时间错位问题

问题背景 #

基于biometric_log表统计不同班次员工每日上下班打卡时间时，夜班（跨天班次）出现打卡时间匹配错位，输出不符合预期。表字段说明：

• empno：员工编号
• punch_code：打卡类型（如1=上班，2=下班）
• punch_datetime：打卡时间

样本数据 #

INSERT INTO biometric_log (empno, punch_code, punch_datetime) VALUES
('E001', 1, '2024-05-20 22:00:00'), -- 夜班上班
('E001', 2, '2024-05-21 06:00:00'), -- 夜班下班
('E002', 1, '2024-05-20 08:00:00'), -- 白班上班
('E002', 2, '2024-05-20 18:00:00'); -- 白班下班

当前错误查询 #

SELECT 
    empno,
    DATE(punch_datetime) AS punch_date,
    MAX(CASE WHEN punch_code = 1 THEN punch_datetime END) AS check_in,
    MAX(CASE WHEN punch_code = 2 THEN punch_datetime END) AS check_out
FROM biometric_log
GROUP BY empno, DATE(punch_datetime);

错误输出 #

期望输出 #

优化查询方案 #

方案1：基于固定夜班时间阈值调整分组日期 #

假设夜班定义为22:00至次日6:00，将该时间段内的打卡归属到前一天的班次日期：

SELECT 
    empno,
    -- 夜班打卡（22:00后）归属到前一天的班次日期
    CASE 
        WHEN TIME(punch_datetime) >= '22:00:00' THEN DATE_SUB(DATE(punch_datetime), INTERVAL 1 DAY)
        ELSE DATE(punch_datetime)
    END AS punch_date,
    MAX(CASE WHEN punch_code = 1 THEN punch_datetime END) AS check_in,
    MAX(CASE WHEN punch_code = 2 THEN punch_datetime END) AS check_out
FROM biometric_log
GROUP BY empno, 
    CASE 
        WHEN TIME(punch_datetime) >= '22:00:00' THEN DATE_SUB(DATE(punch_datetime), INTERVAL 1 DAY)
        ELSE DATE(punch_datetime)
    END;

方案2：关联班次配置表动态判断（更灵活） #

如果存在独立的shift_schedule班次表（包含empno、shift_start、shift_end字段），可以关联该表动态匹配员工的班次归属日期：

SELECT 
    b.empno,
    -- 根据员工班次起始时间判断打卡归属日期
    CASE 
        WHEN TIME(b.punch_datetime) >= s.shift_start THEN DATE(b.punch_datetime)
        ELSE DATE_SUB(DATE(b.punch_datetime), INTERVAL 1 DAY)
    END AS punch_date,
    MAX(CASE WHEN b.punch_code = 1 THEN b.punch_datetime END) AS check_in,
    MAX(CASE WHEN b.punch_code = 2 THEN b.punch_datetime END) AS check_out
FROM biometric_log b
JOIN shift_schedule s ON b.empno = s.empno
GROUP BY b.empno, 
    CASE 
        WHEN TIME(b.punch_datetime) >= s.shift_start THEN DATE(b.punch_datetime)
        ELSE DATE_SUB(DATE(b.punch_datetime), INTERVAL 1 DAY)
    END;

方案说明 #

• 核心逻辑是将跨天的夜班打卡统一归属到班次的起始日期，确保同班次的上下班打卡被分到同一个分组中，避免错位。
• 方案1适用于固定夜班时间的场景，调整TIME(punch_datetime) >= '22:00:00'中的时间阈值即可适配不同夜班时段。
• 方案2适用于员工班次不固定的场景，通过关联班次表实现动态匹配，扩展性更强。

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