如何用SQL查询计算时段时间差、每日总工时及累计总时长？

我来帮你搞定这个考勤记录的时长统计需求！咱们一步步实现你要的功能：计算每个时段的时长、每日总时长，还有所有记录的累计总时长。

核心思路 #

1. 单时段时长计算：用TIMEDIFF()函数直接计算下班时间和上班时间的差值，这个函数专门用来处理TIME类型的时间差。
2. 每日总时长：因为TIME类型直接相加会有溢出问题（比如超过24小时就会重置），所以先把每个时段的时长转成秒数相加，再转回TIME格式。
3. 累计总时长：通过聚合函数SUM计算所有记录的秒数总和，再转成TIME格式，最后用UNION ALL把总计行追加到结果末尾。

完整SQL查询语句 #

下面的语句以MySQL为例（如果是其他数据库比如SQL Server，函数会略有不同，我会在后面说明）：

-- 每日明细行
SELECT
    empid AS `Emp ID`,
    DATE_FORMAT(workdate, '%m-%d-%Y') AS `Date`, -- 格式化日期和示例一致
    timein1 AS `Time In 1`,
    timeout1 AS `Time Out 1`,
    TIMEDIFF(timeout1, timein1) AS `Duration 1`,
    timein2 AS `Time In 2`,
    timeout2 AS `Time Out 2`,
    TIMEDIFF(timeout2, timein2) AS `Duration 2`,
    SEC_TO_TIME(
        -- 处理可能的NULL值，避免计算出错
        IFNULL(TIME_TO_SEC(TIMEDIFF(timeout1, timein1)), 0) + 
        IFNULL(TIME_TO_SEC(TIMEDIFF(timeout2, timein2)), 0)
    ) AS `Daily Total`
FROM tblemptimelog
WHERE empid = 1111

UNION ALL

-- 累计总计行
SELECT
    1111 AS `Emp ID`,
    'Total' AS `Date`,
    NULL AS `Time In 1`,
    NULL AS `Time Out 1`,
    NULL AS `Duration 1`,
    NULL AS `Time In 2`,
    NULL AS `Time Out 2`,
    NULL AS `Duration 2`,
    SEC_TO_TIME(
        SUM(
            IFNULL(TIME_TO_SEC(TIMEDIFF(timeout1, timein1)), 0) + 
            IFNULL(TIME_TO_SEC(TIMEDIFF(timeout2, timein2)), 0)
        )
    ) AS `Daily Total`
FROM tblemptimelog
WHERE empid = 1111

-- 确保总计行在最后
ORDER BY 
    CASE WHEN `Date` = 'Total' THEN 1 ELSE 0 END,
    workdate;

关键函数说明 #

• TIMEDIFF(timeout, timein)：返回两个TIME类型的差值，比如TIMEDIFF('12:00:00', '08:00:00')会得到04:00:00。
• TIME_TO_SEC(time_val)：把TIME类型转成秒数，方便数值计算，避免TIME类型的溢出问题。
• SEC_TO_TIME(seconds)：把累计的秒数转回TIME格式，比如SEC_TO_TIME(28195)会得到07:49:55（对应示例中的第一个每日总时长）。
• IFNULL(expr, 0)：处理可能的NULL值（比如员工只打了上午卡没打下午卡），避免计算时出现NULL结果。

其他数据库适配（可选） #

如果用的是SQL Server，函数会有所不同：

• 用DATEDIFF(SECOND, timein1, timeout1)代替TIME_TO_SEC(TIMEDIFF(timeout1, timein1))
• 用CONVERT(VARCHAR, DATEADD(SECOND, total_seconds, 0), 108)代替SEC_TO_TIME(total_seconds)

查询结果示例 #

执行后你会得到类似这样的结果：

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