计算排除暂停时长的任务总耗时——tblwork表处理疑问

嘿，很高兴你已经找到了解决方案！先给你吃个定心丸：当前的表结构完全可以实现需求，不需要调整表结构——之前得到-6.000的异常结果，大概率是事件匹配的逻辑出了问题。

为什么现有结构可行？ #

你的表已经记录了任务的关键状态节点（开始/暂停/结束）和对应时间，核心逻辑就是按任务分组，把状态事件按时间排序后，关联相邻的事件对，区分出「有效工作时间段」和「暂停时间段」，再累加有效时长即可。

之前异常结果的可能原因 #

出现负数结果，通常是因为没有按时间顺序正确关联前后事件：比如错误地用早的时间减去晚的时间，或者把暂停状态的时间差错误计入了有效时长。

给你一个参考实现思路（SQL为例） #

这里假设表字段为task_id（任务ID）、status（状态）、trigger_time（触发时间）：

1. 先给每个任务的事件按时间排序，并关联下一个事件的状态和时间
2. 筛选出有效工作时间段的时长，排除暂停时间
3. 最后把总时长转换为HH:MM:SS格式

WITH sorted_events AS (
    SELECT 
        task_id,
        status,
        trigger_time,
        -- 获取下一个事件的状态和时间
        LEAD(status) OVER (PARTITION BY task_id ORDER BY trigger_time) AS next_status,
        LEAD(trigger_time) OVER (PARTITION BY task_id ORDER BY trigger_time) AS next_time
    FROM tblwork
)
SELECT 
    task_id,
    -- 把总秒数转换为HH:MM:SS格式
    CONCAT(
        LPAD(FLOOR(SUM(valid_duration)/3600), 2, '0'), ':',
        LPAD(FLOOR((SUM(valid_duration)%3600)/60), 2, '0'), ':',
        LPAD(SUM(valid_duration)%60, 2, '0')
    ) AS total_work_duration
FROM (
    SELECT 
        task_id,
        CASE 
            -- 开始到暂停/结束的时间段，计入有效时长
            WHEN status = 1 AND next_status IN (2,3) THEN TIMESTAMPDIFF(SECOND, trigger_time, next_time)
            -- 暂停到继续的时间段，不计入
            WHEN status = 2 AND next_status = 1 THEN 0
            ELSE 0
        END AS valid_duration
    FROM sorted_events
    WHERE next_time IS NOT NULL  -- 排除最后一条结束事件（没有下一个时间）
) t
GROUP BY task_id;

既然你已经找到正确的实现方法，应该也是基于类似的「事件链关联」逻辑吧？如果有具体的细节想交流或者验证，随时提哦～

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