基于hr_temp_id计算MySQL表历史记录累计时长的问题

嘿，我来帮你搞定这个累计时长的问题！你的SQL结果不符合预期，核心原因是直接对min_id这个时间字符串求和是错误的，而且累计逻辑的排序条件也不对。

问题出在哪？ #

你原来的SQL里，sum(r2.min_id)是把min_id当成普通字符串/数值来相加的——MySQL会自动把00:00:10解析成数字10，00:02:00解析成200（忽略冒号后是000200），所以求和得到10、210这种奇怪的结果，完全不是实际的时长累加。另外，用r2.min_id <= r.min_id做条件是按字符串排序，不是按记录的实际顺序（比如第三条的00:00:10字符串比00:02:00小，会被错误归类到前面）。

正确的实现方法 #

我们需要先把时间字符串转成可计算的秒数，累计求和后再转回时间格式，分两种情况：

1. 如果你用的是MySQL 8.0+（支持窗口函数）

窗口函数是最简洁高效的方式，按hr_temp_id分组，按play_id（记录的顺序）排序，累计计算时长：

SELECT 
    play_id,
    hr_temp_id,
    min_id,
    -- 把秒数转回HH:MM:SS格式
    SEC_TO_TIME(
        -- 累计求和转成秒数的min_id
        SUM(TIME_TO_SEC(min_id)) OVER (
            PARTITION BY hr_temp_id 
            ORDER BY play_id
        )
    ) AS cumulative_duration
FROM 
    ciam_playlist_template
WHERE 
    hr_temp_id = 43;

针对你的示例数据，执行后会得到：

完全符合你要的累计时长效果！

2. 如果你的MySQL版本是5.7及以下（不支持窗口函数）

用自连接的方式实现累计求和：

SELECT 
    r.play_id,
    r.hr_temp_id,
    r.min_id,
    SEC_TO_TIME(SUM(TIME_TO_SEC(r2.min_id))) AS cumulative_duration
FROM 
    ciam_playlist_template r
JOIN 
    ciam_playlist_template r2 
    ON r.hr_temp_id = r2.hr_temp_id 
    AND r2.play_id <= r.play_id -- 按play_id顺序累计
WHERE 
    r.hr_temp_id = 43
GROUP BY 
    r.play_id, r.hr_temp_id, r.min_id
ORDER BY 
    r.play_id;

这个SQL通过自连接，把每条记录和同hr_temp_id下所有更早（play_id更小）的记录关联，然后求和秒数再转成时间格式，结果和上面的窗口函数版本一致。

注意事项 #

• 确保play_id是按记录的时间顺序递增的，如果你的实际数据有其他排序逻辑，把ORDER BY play_id改成对应的排序字段（比如创建时间字段）。
• TIME_TO_SEC()和SEC_TO_TIME()是MySQL处理时间格式的专用函数，能准确把HH:MM:SS转成秒数再转回时间。

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