MySQL多表关联后按Env_name分组对TIMEDIFF结果求和计算工单总开放时长的问题咨询

问题的核心在于：TIMEDIFF返回的是TIME类型，直接用SUM()函数对其求和会出现异常（MySQL对TIME类型的求和逻辑并非累加总时长，而是按时间字段的数值相加，且TIME类型本身存在范围限制）。正确的做法是先将时间差转换为总秒数，求和后再转换回时分秒格式。

具体实现步骤 #

1. 将时间差转为总秒数：使用TIME_TO_SEC()函数，把TIMEDIFF(close_date, created_date)的结果转换成以秒为单位的数值，这样就能正常执行求和操作。
2. 分组求和秒数：按Env_name分组，对转换后的秒数执行SUM()操作，得到每个环境的总开放秒数。
3. 将总秒数转回时分秒：用SEC_TO_TIME()函数把求和后的总秒数还原为可读的时间格式。
4. 避免重复统计工单：由于多表关联可能导致同一张工单被多次匹配返回（比如一个工单关联多个unit），需要先通过子查询确保每个工单只被计算一次。

完整SQL语句 #

SELECT 
    Env_name,
    SEC_TO_TIME(SUM(total_seconds)) AS Total_open_time
FROM (
    SELECT DISTINCT
        u_db.environments.name AS Env_name,
        TIME_TO_SEC(TIMEDIFF(u_db.tickets.close_date, u_db.tickets.created_date)) AS total_seconds
    FROM u_db.tickets 
    INNER JOIN u_db.ticket_units ON u_db.tickets.id = u_db.ticket_units.ticket_id 
    INNER JOIN u_db.units ON u_db.ticket_units.unit_id = u_db.units.id 
    INNER JOIN u_db.locations ON u_db.units.location_id = u_db.locations.id 
    INNER JOIN u_db.location_groups ON u_db.locations.locations_group_id = u_db.location_groups.id 
    INNER JOIN u_db.environments ON u_db.location_groups.environment = u_db.environments.id 
    WHERE u_db.tickets.created_date >= '2021-09-01 00:00:00' 
      AND u_db.tickets.created_date < '2021-10-01 00:00:00' 
      AND u_db.location_groups.id IN (50,17,46,45,48,49) 
      AND u_db.tickets.id IN (132357,132361,132372,132473)
) AS ticket_time_summary
GROUP BY Env_name;

说明 #

• 子查询里的DISTINCT确保每个工单只被计算一次，避免关联多张表时重复统计同一个工单的时长。
• TIME_TO_SEC和SEC_TO_TIME是MySQL处理跨天时间差求和的标准组合，能完美支持超过24小时的时长累加（比如你的案例中GR组的54小时）。

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