我正在尝试创建一个查询来按用户分组的时间总和,结果应该是几分钟。
表内容:
duration user 00:06:00 user1 00:06:00 user2 00:04:00 user3 00:01:00 user55 00:02:00 user1 00:01:00 user2 00:01:00 user55
结果预期:
duration user 8 user1 7 user2 4 user3 2 user55
这是我越接近:
SELECT user, SEC_TO_TIME( SUM( TIME_TO_SEC( ) ) ) AS totaltime from salientes group by user SELECT user, SEC_TO_TIME( SUM( TIME_TO_SEC(持续时间) ) ) AS totaltime from salientes group by user时间
然而,这将结果输出为000:00:00,在某些情况下,结果为838:59:59。
任何帮助都感激不尽。
I'm trying to create a query to sum time grouped by user and the result should be in minutes.
contents of table:
duration user 00:06:00 user1 00:06:00 user2 00:04:00 user3 00:01:00 user55 00:02:00 user1 00:01:00 user2 00:01:00 user55
Result expected:
duration user 8 user1 7 user2 4 user3 2 user55
This is the closer I can get:
SELECT user, SEC_TO_TIME( SUM( TIME_TO_SEC(duration) ) ) AS totaltime from salientes group by user
However this outputs the result as 000:00:00 and for some cases, this ends on 838:59:59.
Any help will be much appreciated.
最满意答案
mysql> SELECT MINUTE(SEC_TO_TIME(SUM(TIME_TO_SEC( duration)))) as tt ,user from tt group by user; +------+--------+ | tt | user | +------+--------+ | 8 | user1 | | 7 | user2 | | 4 | user3 | | 2 | user55 | +------+--------+ 4 rows in set (0.00 sec) mysql> SELECT MINUTE(SEC_TO_TIME(SUM(TIME_TO_SEC( duration)))) as tt ,user from tt group by user; +------+--------+ | tt | user | +------+--------+ | 8 | user1 | | 7 | user2 | | 4 | user3 | | 2 | user55 | +------+--------+ 4 rows in set (0.00 sec)更多推荐
发布评论