java – 计算mysql中的总登录注销时间

mysql select * from timing;+--------------+---------------------+-----------------+| employeeIdNo | employeeLogTime | employeeLogType |+--------------+---------------------+-----------------+|...

mysql> select * from timing;

+--------------+---------------------+-----------------+
| employeeIdNo | employeeLogTime     | employeeLogType |
+--------------+---------------------+-----------------+
|            1 | 2011-08-16 14:59:08 | login           |
|            1 | 2011-08-16 15:00:06 | logout          |
|            1 | 2011-08-16 15:14:51 | login           |
|            2 | 2011-08-16 15:15:00 | login           |
|            1 | 2011-08-16 15:59:01 | logout          |
|            2 | 2011-08-16 15:59:09 | logout          |
+--------------+---------------------+-----------------+

我想显示使用上述数据的员工的总工作时间.所以我想要一个mysql查询来计算(比方说)employeeIdNo = 1的总时间.我的表名是timing,employeeIdNo是表employee的外键引用.在计算之后它应该返回totalLogTime值= 00:45:52(这是大约值,我没有完全计算它).请提供适当的查询.提前致谢.

我试过这个问题.:-

mysql> SELECT 
  TIMEDIFF(FIRSTTIME.employeeLogTime,SECONDTIME.employeeLogTime) 
  FROM (
    (SELECT * FROM timing HAVING employeeLogTime = max(employeeLogTime)) as FIRSTTIME,     
    (SELECT * FROM timing ORDER BY employeeLogTime LIMIT 1,1) as SECONDTIME);
Empty set (0.01 sec)

它给我空集如上图所示.

解决方法:

难道你不能为单个用户总结所有时间：

> for login(以毫秒为单位)调用此A.
> for logout(以毫秒为单位)调用此B

并返回B – A;

作为一种查看它的方式有点尴尬,但提供了更简单的SQL查询.
您还需要使用此方法检查登录和注销是否具有相同的出现次数.