SQLite计算多行间时间差实现员工考勤工时统计查询

安全阿岳

2026-9-26

SQLite 员工累计工时查询方案

前提说明

attendanceTable的id字段为员工ID，关联user表的主键id
考勤数据已按mydate升序排列，默认上下班记录成对出现无缺卡
两个查询参数:start_date、:end_date格式为YYYY-MM-DD，例如2021-08-20

实现思路

用LEAD窗口函数按员工ID分组，匹配每条上班记录对应的下一条下班记录
计算每段上下班的时间差，累加得到员工总工时秒数
将总秒数格式化转为HH:MM格式输出，天然支持跨天工时计算

完整查询语句

WITH attendance_pair AS (
    SELECT
        id,
        mydate AS start_time,
        startJob,
        -- 取下一条记录的时间和考勤状态
        LEAD(mydate) OVER (PARTITION BY id ORDER BY mydate) AS end_time,
        LEAD(startJob) OVER (PARTITION BY id ORDER BY mydate) AS next_startJob
    FROM attendanceTable
    -- 过滤考勤时间在查询范围内的记录
    WHERE mydate >= :start_date
      AND mydate < DATE(:end_date, '+1 day')
)
SELECT
    u.id,
    u.name,
    -- 总秒数转HH:MM格式
    PRINTF('%02d:%02d', 
           CAST(SUM(STRFTIME('%s', end_time) - STRFTIME('%s', start_time)) / 3600 AS INTEGER),
           CAST((SUM(STRFTIME('%s', end_time) - STRFTIME('%s', start_time)) % 3600) / 60 AS INTEGER)
    ) AS userWorkedTime
FROM attendance_pair ap
JOIN user u ON ap.id = u.id
-- 只取上下班成对的有效记录
WHERE ap.startJob = 1 AND ap.next_startJob = 0
GROUP BY u.id, u.name
ORDER BY u.id;