mysql - 如何按ID统计MySQL组中的时间？

Question

如何在MySQL中计算时间？所以我有如下表:

id | time     |
01 | 00:36:00 |
01 | 01:00:00 |
02 | 00:30:00 |

我需要时间按 id 来计算该组，如下所示:

id | time     |
01 | 01:36:00 |
02 | 00:30:00 |

Answer 1

我认为你想要的是总和

SELECT 
RIGHT('0' + CAST (FLOOR(COALESCE (SUM(
        datediff(second,
                cast('1900-01-01' as datetime),
                cast(time as datetime)
               )/3600.0), 0) / 60) AS VARCHAR (8)), 2)
               + ':' +
               RIGHT('0' + CAST (FLOOR(COALESCE (SUM(
        datediff(second,
                cast('1900-01-01' as datetime),
                cast(time as datetime)
               )/3600.0), 0) % 60) AS VARCHAR (2)), 2) + ':' + 
                RIGHT('0' + CAST (FLOOR((SUM(
        datediff(second,
                cast('1900-01-01' as datetime),
                cast(time as datetime)
               )/3600.0)* 60) % 60) AS VARCHAR (2)), 2)
             as DecimalHours
, ID
FROM #t 
group by ID 

我所做的就是首先将您的时间转换为十进制(这样我们就可以执行SUM)，然后将总和格式化为时间格式。