MySQL中如何根据考勤时间跨日期获取对应出退时间

问题背景 #

现有MySQL表Att_Log结构及数据如下：

需要实现考勤统计，输出规则为：

• 按员工和日期分组，In_Time取当日最早打卡时间
• 若In_Time≥21:00:00，Out_Time取该员工次日的最早打卡时间；否则取当日最晚打卡时间

期望输出结果：

解决方案SQL语句 #

WITH daily_att AS (
    -- 计算每个员工每日的最早打卡（上班时间）和当日最晚打卡（正常下班候选）
    SELECT 
        Emp_ID,
        Date_Att,
        MIN(Time_Att) AS In_Time,
        MAX(Time_Att) AS same_day_out
    FROM Att_Log
    GROUP BY Emp_ID, Date_Att
),
next_day_att AS (
    -- 计算每个员工每日对应的次日最早打卡（跨天下班候选）
    SELECT 
        Emp_ID,
        DATE_ADD(Date_Att, INTERVAL 1 DAY) AS next_date,
        MIN(Time_Att) AS next_day_out
    FROM Att_Log
    GROUP BY Emp_ID, Date_Att
)
-- 关联两组数据，根据规则选择下班时间
SELECT 
    d.Emp_ID,
    d.Date_Att AS Date,
    d.In_Time,
    CASE 
        WHEN d.In_Time >= '21:00:00' THEN n.next_day_out
        ELSE d.same_day_out
    END AS Out_Time
FROM daily_att d
LEFT JOIN next_day_att n 
    ON d.Emp_ID = n.Emp_ID 
    AND d.Date_Att = n.next_date
ORDER BY d.Emp_ID, d.Date_Att;

逻辑说明 #

1. daily_att公共表表达式：先按员工和日期分组，提取当日最早打卡时间作为In_Time，同时提取当日最晚打卡时间作为正常情况的下班时间候选。
2. next_day_att公共表表达式：同样按员工和日期分组，将日期加1天作为关联标识，提取当日的最早打卡时间，这就是对应前一天跨天考勤的下班时间候选。
3. 主查询：通过员工ID和日期关联两个公共表，使用CASE WHEN判断规则：如果上班时间晚于21点，就取次日的最早打卡时间作为下班时间；否则使用当日最晚打卡时间。

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