如何在MySQL中按相同ID统计指定时段内有效工时（小时）

针对你要统计用户ID'57'4月份有效工时的需求，这里提供两种实用的SQL写法，解决你提到的匹配打卡记录和汇总时长的问题：

方法1：使用窗口函数（推荐，适合新手） #

窗口函数LEAD()可以直接获取同用户下一条打卡记录的时间和类型，完美匹配开始/结束打卡：

SELECT 
    uid,
    SUM(DATEDIFF(hour, dtime, next_dtime)) AS total_work_hours
FROM (
    -- 子查询：获取每条记录的下一条打卡时间和类型
    SELECT 
        uid,
        dtime,
        chkin_type,
        LEAD(dtime) OVER (PARTITION BY uid ORDER BY dtime) AS next_dtime,
        LEAD(chkin_type) OVER (PARTITION BY uid ORDER BY dtime) AS next_type
    FROM checkin_db
    WHERE uid = '57' AND MONTH(dtime) = '4'
) AS sub_query
-- 只保留开始打卡，且下一条是结束打卡的记录
WHERE chkin_type = 1 AND next_type = 2
GROUP BY uid;

关键解释： #

• PARTITION BY uid：确保只在同一个用户的记录里找下一条
• ORDER BY dtime：按打卡时间顺序匹配，保证开始打卡后紧跟的是对应的结束打卡
• 外层筛选chkin_type=1 AND next_type=2：只统计有对应结束记录的有效打卡
• SUM(DATEDIFF(...))：把所有有效打卡的时长累加，得到总工时

方法2：使用自连接（更严谨，适合复杂打卡场景） #

如果你的打卡记录可能存在中间插其他记录的情况，用自连接+排除中间记录的方式更准确：

SELECT 
    c1.uid,
    SUM(DATEDIFF(hour, c1.dtime, c2.dtime)) AS total_work_hours
FROM checkin_db c1
-- 关联同用户的结束打卡记录
JOIN checkin_db c2 
    ON c1.uid = c2.uid
    AND c2.chkin_type = 2
    AND c2.dtime > c1.dtime
    -- 确保两条记录之间没有其他打卡记录，避免错误匹配
    AND NOT EXISTS (
        SELECT 1 
        FROM checkin_db c3
        WHERE c3.uid = c1.uid
        AND c3.dtime BETWEEN c1.dtime AND c2.dtime
        AND c3.id NOT IN (c1.id, c2.id)
    )
WHERE c1.uid = '57' 
  AND MONTH(c1.dtime) = '4'
  AND c1.chkin_type = 1
GROUP BY c1.uid;

关键解释： #

• JOIN checkin_db c2：把开始打卡记录（c1）和结束打卡记录（c2）关联起来
• NOT EXISTS子查询：过滤掉中间有其他打卡的情况，保证每对开始/结束是连续的有效记录
• 同样用SUM(DATEDIFF(...))汇总总工时

针对你两个问题的直接解答： #

1. 匹配开始/结束记录：
   • 用LEAD()窗口函数是最简单的方式，按时间顺序自动匹配同用户的下一条记录；
   • 自连接则通过关联条件+排除中间记录，实现更严谨的匹配。
2. 汇总工时：
   • 先用DATEDIFF(hour, 开始时间, 结束时间)计算单次打卡的时长，再用SUM()函数把所有有效时长累加，最后用GROUP BY uid（这里仅针对单个用户，可省略，但保留更规范）。

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