从不同的mysql表中获取时间总和 [英] Get sum of time from different mysql tables

问题描述

有可能吗?如果可以，怎么办? 从这样的2个或更多表中获取时间总和:

Is it possible and if yes, how? to get total sum of time from 2 or more tables like this:

表1

id, racer_nr, total_time_spend
1, 315 , 03:01:40
1, 312 , 06:01:40
1, 313 , 10:01:40

表2

id, racer_nr, total_time_spend
1, 315 , 07:01:40
1, 312 , 15:15:00
1, 313 , 10:01:40

需要从所有表中获取每个racer_nr的total_time_spend的总和. 至少有5个，只是给了您2个以简化操作

need to get sum of total_time_spend of each racer_nr from all the tables. Have 5 of them atleast, just gave you 2 for simplier

推荐答案

drop table if exists t,t2;

create table t(id int, racer_nr int, total_time_spend time);
insert into t values
(1, 315 , '03:01:40'),
(1, 312 , '06:01:40'),
(1, 313 , '10:01:40');
create table t2(id int, racer_nr int, total_time_spend time);
insert into t2 values
(1, 315 , '07:01:40'),
(1, 312 , '15:15:00'),
(1, 313 , '10:01:40');



select t.racer_nr, sec_to_time(sum(spendsecs))
from
(
select t.racer_nr , time_to_sec(total_time_spend) spendsecs
from t 
union all
select t.racer_nr , time_to_sec(total_time_spend) spendsecs
from t2 t
) t
group by racer_nr

+----------+-----------------------------+
| racer_nr | sec_to_time(sum(spendsecs)) |
+----------+-----------------------------+
|      312 | 21:16:40                    |
|      313 | 10:01:40                    |
|      315 | 10:03:20                    |
+----------+-----------------------------+
3 rows in set (0.00 sec)

在此处阅读日期时间功能 https://dev.mysql.com/doc/refman/5.7/zh-CN/date-and-time-functions.html

Read up on date time functions here https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

这篇关于从不同的mysql表中获取时间总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！