我正在使用MySQL 5.6,并希望通过SQL语句根据以下数据计算总和(以分钟为单位):
第三列仅提供信息,意味着我期待 15 个值
Status Date_Time Total_hours
LogIn 2016-01-14 00:00:00
LogOut 2016-01-14 09:00:00 9
LogIn 2016-01-14 10:00:00
LogOut 2016-01-14 14:00:00 4
LogIn 2016-01-14 16:00:00
LogOut 2016-01-14 18:00:00 2
hours Total ==>> 15
更新以包含表和字段名称。
SELECT
loggedin.InOutStatus,
MAX(loggedin.Date_Time) AS start_time,
loggedout.Date_TimeAS end_time,
TIMEDIFF(MAX(loggedin.Date_Time), loggedout.Date_Time)
FROM empstatuslog AS loggedin
INNER JOIN empstatuslog AS loggedout ON (loggedin.InOutStatus = loggedout.InOutStatus
AND loggedout.Date_Time > loggedin.Date_Time)
WHERE loggedin.InOutStatus= 'LogIn'
AND loggedout.InOutStatus= 'LogOut'
GROUP BY loggedin.InOutStatus