mysql> select * from timing;
+--------------+---------------------+-----------------+
| employeeIdNo | employeeLogTime | employeeLogType |
+--------------+---------------------+-----------------+
| 1 | 2011-08-16 09:53:29 | login |
| 1 | 2011-08-16 10:45:42 | logout |
| 1 | 2011-08-16 10:55:29 | login |
| 1 | 2011-08-16 17:55:39 | logout |
+--------------+---------------------+-----------------+
我想用以上数据显示员工的总工作时间。所以我想要一个mysql查询来计算employeeIdNo=1的总时间。我的表名是timing, employeeIdNo是表employee的外键引用。计算后,它应该返回totalLogTime值= 7:52:23。请给我适当的查询。
a'r的建议很有道理。另外,在注销时计算每个会话的持续时间是一种更好的方法—尽管这不是严格规范化的,但它分散了计算....
的负载。SELECT ilv.employee,
SUM(UNIX_TIMESTAMP(logouttime)
- IF(logintime IS NULL,
UNIX_TIMESTAMP(logouttime),
UNIX_TIMESTAMP(logintime)))
FROM (
SELECT a.employeeId, a.time AS logouttime,
(SELECT MAX(b.time)
FROM log b
WHERE b.employeeId=a.employeeId
AND b.time<a.time
AND b.type='login') as logintime
FROM log a
WHERE a.type='logout'
AND a.time BETWEEN <range start> AND <range end>
AND employeeId=1 /* optional */
) ilv
GROUP BY ilv.employeeId