mysql - 如何根据每个empid获取时间差，得到exists total 50

Question

我得到了:

id empid Ftime Stime 这是字段。

 1 01 2017-03-04 07:00:00       2017-03-04 23:45:00 
 2 01 2017-03-05 05:14:12       2017-03-05 21:49:03 
 3 01 2017-03-06 06:08:16       2017-03-06 22:58:13
 4 01 2017-03-07 04:12:19       2017-03-07 23:35:20 
 5 01 2017-03-08 06:17:17       2017-03-08 23:48:19
 6 02 2017-03-04 06:40:11       2017-03-04 22:45:00 
 7 02 2017-03-05 05:19:23       2017-03-05 22:49:03 
 8 02 2017-03-06 07:08:58       2017-03-06 23:58:13
 9 02 2017-03-07 03:12:19       2017-03-07 22:35:20 
10 02 2017-03-08 07:19:12      2017-03-08 23:48:19

如果总计 = 54 小时，那么我需要 4 用于 empid 01 等等；

我需要的结果是这样的

empid total hours
01     10
02     20

类似的东西。

提前致谢。

Answer 1

试试这个我包括分钟:

select empid,sum(hours)-50,(sum(mins)/60 + sum(sec)/60) mins from
(select empid, TIMEDIFF(stime,ftime),
SUBSTRING(TIMEDIFF(stime,ftime) FROM 1 FOR 2) as hours,
SUBSTRING(TIMEDIFF(stime,ftime) FROM 4 FOR 2) as mins,
SUBSTRING(TIMEDIFF(stime,ftime) FROM 7 FOR 2) as sec
 from yourtable) as a group by empid