如何用SQL按日期统计每位员工的总工时（小时+分钟）？

假设你的打卡数据表名为attendance，包含字段：employee_id（员工ID）、datetime（打卡时间）、type（打卡类型，0=上班，1=下班），以下是针对不同SQL环境的实现方案：

核心思路 #

先匹配每位员工单日的上下班记录对，计算单次打卡的时长，再按员工+日期维度汇总总时长，最终拆分出小时和分钟数。

通用SQL实现（兼容多数数据库） #

利用窗口函数LEAD()关联同员工同日期的下一条打卡记录（上班对应下班）：

WITH attendance_pairs AS (
    SELECT
        employee_id,
        DATE(datetime) AS work_date,
        datetime AS in_time,
        -- 获取同员工同日期的下一条打卡记录（即下班时间）
        LEAD(datetime) OVER (PARTITION BY employee_id, DATE(datetime) ORDER BY datetime) AS out_time,
        type
    FROM attendance
)
SELECT
    employee_id,
    work_date,
    -- 计算总小时数：总分钟数整除60
    FLOOR(SUM(TIMESTAMPDIFF(MINUTE, in_time, out_time)) / 60) AS total_hours,
    -- 计算剩余分钟数：总分钟数取余60
    SUM(TIMESTAMPDIFF(MINUTE, in_time, out_time)) % 60 AS total_minutes
FROM attendance_pairs
-- 仅筛选上班记录，且确保有对应的下班记录
WHERE type = 0 AND out_time IS NOT NULL
GROUP BY employee_id, work_date
ORDER BY employee_id, work_date;

分数据库适配版本 #

1. MySQL 专属实现 #

如果不使用窗口函数，可通过子查询匹配对应下班记录：

SELECT
    employee_id,
    DATE(datetime) AS work_date,
    FLOOR(SUM(TIMESTAMPDIFF(MINUTE, in_time, out_time)) / 60) AS total_hours,
    SUM(TIMESTAMPDIFF(MINUTE, in_time, out_time)) % 60 AS total_minutes
FROM (
    SELECT
        a1.employee_id,
        a1.datetime AS in_time,
        -- 匹配同员工同日期、晚于上班时间的最早下班记录
        (SELECT MIN(a2.datetime) 
         FROM attendance a2 
         WHERE a2.employee_id = a1.employee_id 
           AND DATE(a2.datetime) = DATE(a1.datetime) 
           AND a2.type = 1 
           AND a2.datetime > a1.datetime) AS out_time
    FROM attendance a1
    WHERE a1.type = 0
) AS paired_records
WHERE out_time IS NOT NULL
GROUP BY employee_id, work_date;

2. SQL Server 专属实现 #

替换日期函数和时长计算函数：

WITH attendance_pairs AS (
    SELECT
        employee_id,
        CAST(datetime AS DATE) AS work_date,
        datetime AS in_time,
        LEAD(datetime) OVER (PARTITION BY employee_id, CAST(datetime AS DATE) ORDER BY datetime) AS out_time
    FROM attendance
    WHERE type = 0
)
SELECT
    employee_id,
    work_date,
    FLOOR(SUM(DATEDIFF(MINUTE, in_time, out_time)) / 60) AS total_hours,
    SUM(DATEDIFF(MINUTE, in_time, out_time)) % 60 AS total_minutes
FROM attendance_pairs
WHERE out_time IS NOT NULL
GROUP BY employee_id, work_date
ORDER BY employee_id, work_date;

注意事项 #

• 若存在员工单日多次上下班（如外出办事再返回），上述逻辑会自动累加所有时段的时长
• 对于缺失下班记录的情况，可根据业务需求调整（如忽略该条上班记录，或按固定时长统计）
• 日期处理函数需根据数据库调整：Oracle用TRUNC(datetime)，PostgreSQL用DATE(datetime)

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