MariaDB中计算员工指定日期有效打卡时长的SQL实现需求

需求：在MariaDB 10.6.4环境下计算员工指定日期的有效工作时长，规则为仅统计配对成功的PunchTypeID=1（打卡入）与PunchTypeID=2（打卡出）的时间差之和，未配对的打卡记录直接丢弃。要求用纯SQL实现，方便嵌入部门月度总工时统计等其他查询。

表结构 #

示例数据 #

执行查询：

SELECT TIME(PunchTime), PunchTypeID FROM TimeTable;

返回结果：

期望输出：07:30

解决方案SQL #

以下两种方案均能实现需求，可根据实际场景选择：

方案一：利用LEAD窗口函数配对

SELECT 
    SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, punch_in.PunchTime, punch_out.PunchTime))) AS total_work_hours
FROM (
    SELECT 
        PunchTime,
        EmployeeID,
        LEAD(PunchTime) OVER (PARTITION BY EmployeeID, DATE(PunchTime) ORDER BY PunchTime) AS next_punch_time
    FROM TimeTable
    WHERE DATE(PunchTime) = '2024-05-20' -- 替换为目标日期
      AND EmployeeID = 1001 -- 替换为目标员工ID
      AND PunchTypeID = 1
) AS punch_in
JOIN TimeTable AS punch_out 
    ON punch_in.EmployeeID = punch_out.EmployeeID
    AND punch_out.PunchTime = punch_in.next_punch_time
    AND punch_out.PunchTypeID = 2;

方案二：按排序行号配对

SELECT 
    SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, t1.PunchTime, t2.PunchTime))) AS total_work_hours
FROM (
    SELECT 
        PunchID,
        PunchTime,
        EmployeeID,
        ROW_NUMBER() OVER (PARTITION BY EmployeeID, DATE(PunchTime) ORDER BY PunchTime) AS row_num
    FROM TimeTable
    WHERE DATE(PunchTime) = '2024-05-20' -- 替换为目标日期
      AND EmployeeID = 1001 -- 替换为目标员工ID
) AS t1
JOIN (
    SELECT 
        PunchTime,
        EmployeeID,
        ROW_NUMBER() OVER (PARTITION BY EmployeeID, DATE(PunchTime) ORDER BY PunchTime) AS row_num
    FROM TimeTable
    WHERE DATE(PunchTime) = '2024-05-20' -- 替换为目标日期
      AND EmployeeID = 1001 -- 替换为目标员工ID
) AS t2 
    ON t1.EmployeeID = t2.EmployeeID
    AND t1.row_num + 1 = t2.row_num
WHERE t1.PunchTypeID = 1
  AND t2.PunchTypeID = 2;

说明 #

• 两种方案均通过配对入/出打卡记录计算时间差，自动丢弃未配对的记录。
• 替换SQL中的日期和员工ID参数即可适配不同场景，若需批量统计（如部门月度总工时），可移除EmployeeID过滤条件，按EmployeeID或部门分组聚合。

内容的提问来源于stack exchange，提问作者Bee S.