MySQL考勤表日期范围查询：关联IN/OUT记录并补全日期

表结构与示例数据 #

考勤表attendance的结构及示例数据如下：

查询规则 #

1. DateTime_In取自Status = 'IN'的Date_Time字段值
2. 若DateTime_In的时间部分**< 22:00:00**，DateTime_Out取当日Status = 'OUT'的Date_Time
3. 若DateTime_In的时间部分**>= 22:00:00**，DateTime_Out取次日Status = 'OUT'的Date_Time
4. 指定日期范围内无对应记录的日期，所有字段显示NULL
5. 2024-02-02这类跨天考勤记录的三种可接受格式已被规则覆盖，无需额外处理

MySQL查询语句 #

WITH date_range AS (
    -- 替换此处的起止日期以调整查询范围
    SELECT DATE('2024-02-01') AS att_date
    UNION ALL
    SELECT att_date + INTERVAL 1 DAY
    FROM date_range
    WHERE att_date < DATE('2024-02-05')
)
SELECT
    dr.att_date,
    a_in.Employee,
    a_in.Date_Time AS DateTime_In,
    -- 根据上班打卡时间匹配对应下班记录
    CASE
        WHEN TIME(a_in.Date_Time) < '22:00:00' THEN a_out_same.Date_Time
        ELSE a_out_next.Date_Time
    END AS DateTime_Out
FROM date_range dr
LEFT JOIN attendance a_in
    ON dr.att_date = DATE(a_in.Date_Time) AND a_in.Status = 'IN'
-- 关联当日下班记录
LEFT JOIN attendance a_out_same
    ON dr.att_date = DATE(a_out_same.Date_Time) 
    AND a_out_same.Status = 'OUT'
    AND a_out_same.Employee = a_in.Employee
-- 关联次日下班记录
LEFT JOIN attendance a_out_next
    ON (dr.att_date + INTERVAL 1 DAY) = DATE(a_out_next.Date_Time) 
    AND a_out_next.Status = 'OUT'
    AND a_out_next.Employee = a_in.Employee
ORDER BY dr.att_date;

语句说明 #

1. 日期序列生成：通过递归CTE date_range生成指定起止日期之间的所有日期，确保无考勤记录的日期也能出现在结果中
2. 关联上班记录：用LEFT JOIN匹配对应日期的上班打卡（Status='IN'）记录
3. 匹配下班记录：根据上班打卡时间的早晚，分别关联当日或次日的下班打卡（Status='OUT'）记录
4. 空值处理：LEFT JOIN保证即使某日期无考勤数据，仍会显示该日期，对应字段自动填充NULL

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