MySQL计算跨午夜/多日排班的每日工时总和

针对带跨午夜、超24小时班次、当日多次打卡场景的员工工时统计需求，以下是适配的MySQL查询方案，支持按自然日统计并转换为任意时间单位。

核心思路 #

1. 配对打卡记录：将每个上班（in_out=1）记录与对应的下班（in_out=0）记录配对，处理未下班的收尾情况。
2. 生成日期范围：递归生成从最早打卡日到最晚打卡日的所有自然日，避免遗漏无打卡但有工时的日期（如示例中的8月24日）。
3. 拆分跨天工时：将跨多天的班次拆分为对应自然日的工时，完整天数按24小时计算，首尾天数按实际时段计算。

完整SQL代码 #

WITH clock_pairs AS (
    -- 为每个上班记录配对后续的下班记录
    SELECT
        User_id,
        Date_time AS start_time,
        LEAD(Date_time) OVER (PARTITION BY User_id ORDER BY Date_time) AS end_time,
        in_out
    FROM attendance
    WHERE User_id = 1 -- 替换为指定的用户ID
),
valid_pairs AS (
    -- 过滤有效上班记录，处理未下班的情况（此处用当前时间填充，可按需调整为当日结束时间）
    SELECT
        User_id,
        start_time,
        COALESCE(end_time, CURRENT_TIMESTAMP) AS actual_end_time
    FROM clock_pairs
    WHERE in_out = 1
),
date_range AS (
    -- 递归生成需要统计的所有自然日
    WITH RECURSIVE dates AS (
        SELECT DATE(MIN(start_time)) AS day FROM valid_pairs
        UNION ALL
        SELECT day + INTERVAL 1 DAY
        FROM dates
        WHERE day + INTERVAL 1 DAY <= (SELECT DATE(MAX(actual_end_time)) FROM valid_pairs)
    )
    SELECT day FROM dates
),
daily_hours AS (
    -- 按自然日拆分计算工时（单位：秒）
    SELECT
        dr.day,
        SUM(
            CASE
                -- 班次完全在当日
                WHEN DATE(vp.start_time) = dr.day AND DATE(vp.actual_end_time) = dr.day THEN
                    TIMESTAMPDIFF(SECOND, vp.start_time, vp.actual_end_time)
                -- 班次从当日开始，次日及以后结束：计算当日剩余时长
                WHEN DATE(vp.start_time) = dr.day THEN
                    TIMESTAMPDIFF(SECOND, vp.start_time, DATE_ADD(dr.day, INTERVAL 1 DAY))
                -- 班次从之前日期开始，当日结束：计算当日起始到下班的时长
                WHEN DATE(vp.actual_end_time) = dr.day THEN
                    TIMESTAMPDIFF(SECOND, dr.day, vp.actual_end_time)
                -- 班次覆盖整个当日：按24小时计算
                ELSE 86400
            END
        ) AS total_seconds
    FROM date_range dr
    LEFT JOIN valid_pairs vp ON dr.day BETWEEN DATE(vp.start_time) AND DATE(vp.actual_end_time)
    GROUP BY dr.day
)
-- 转换为时分秒格式输出，可按需调整为分钟/小时单位
SELECT
    day AS `Day`,
    SEC_TO_TIME(total_seconds) AS `hours_worked`
FROM daily_hours
ORDER BY day;

关键调整说明 #

• 时间单位转换：若需要分钟/小时单位，可将TIMESTAMPDIFF(SECOND, ...)改为TIMESTAMPDIFF(MINUTE, ...)或TIMESTAMPDIFF(HOUR, ...)，同时将SEC_TO_TIME(total_seconds)替换为total_seconds/60（分钟）或total_seconds/3600（小时）。
• 未下班处理：若需将未下班的班次计算到当日结束，将COALESCE(end_time, CURRENT_TIMESTAMP)改为COALESCE(end_time, DATE_ADD(DATE(start_time), INTERVAL 1 DAY))。
• 用户范围：移除WHERE User_id = 1可统计所有用户的工时，需在最终SELECT中加入User_id分组。

示例结果验证 #

针对提供的示例打卡数据，执行上述SQL后将得到与期望一致的结果，自动处理跨午夜、超24小时班次及当日多次打卡的场景。

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