MySQL查询：从考勤表获取关联上下班记录及跨日出勤数据

原始考勤表（表名：attendance） #

期望查询结果 #

转换规则 #

• DateTime_In 取自 Status 为 IN 的 Date_Time 值
• 若 DateTime_In 时间部分 < 22:00:00，DateTime_Out 取同日期Status为OUT的Date_Time
• 若 DateTime_In 时间部分 >= 22:00:00，DateTime_Out 取次日Status为OUT的Date_Time
• 需覆盖所有存在考勤记录的日期（含仅存在OUT记录的日期）

MySQL 查询语句 #

WITH all_dates AS (
    -- 提取所有有考勤记录的日期
    SELECT DISTINCT DATE(Date_Time) AS record_date
    FROM attendance
),
in_records AS (
    -- 处理IN记录，标记对应的OUT所属日期
    SELECT 
        Employee,
        DATE(Date_Time) AS in_date,
        Date_Time AS DateTime_In,
        CASE 
            WHEN TIME(Date_Time) >= '22:00:00' THEN DATE(Date_Time) + INTERVAL 1 DAY
            ELSE DATE(Date_Time)
        END AS out_date
    FROM attendance
    WHERE Status = 'IN'
),
out_records AS (
    -- 整理OUT记录，按日期分组
    SELECT 
        Employee,
        DATE(Date_Time) AS out_date,
        Date_Time AS DateTime_Out
    FROM attendance
    WHERE Status = 'OUT'
)
-- 关联所有日期、IN记录、OUT记录，生成最终结果
SELECT 
    COALESCE(ir.Employee, or_.Employee) AS Employee,
    ad.record_date AS Date,
    ir.DateTime_In,
    or_.DateTime_Out
FROM all_dates ad
LEFT JOIN in_records ir ON ad.record_date = ir.in_date
LEFT JOIN out_records or_ ON 
    -- 匹配IN记录对应的OUT日期，或匹配无IN记录的日期的OUT记录
    (ir.Employee = or_.Employee AND ir.out_date = or_.out_date)
    OR (ir.Employee IS NULL AND ad.record_date = or_.out_date)
ORDER BY ad.record_date;

语句说明 #

1. all_dates：确保结果包含所有有考勤动作的日期，避免遗漏仅存在OUT记录的日期
2. in_records：根据IN时间判断该次打卡对应的OUT记录应该属于当天还是次日，为后续关联做准备
3. out_records：单独提取OUT记录，方便按日期关联
4. 最终关联时，通过COALESCE确保员工字段不为空，同时处理两种关联场景：有IN记录的日期关联对应OUT，无IN记录的日期直接关联当天的OUT

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