目前我们在mysql中有一个表,其定义如下。
CREATE TABLE `tblStaffLog` (
`SlNo` Int( 11 ) AUTO_INCREMENT NOT NULL,
`UserName` VarChar( 20 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`PageName` VarChar( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`LogType` VarChar( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`Extension` VarChar( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`LogTime` DateTime NOT NULL,
`IPAddress` VarChar( 20 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`activity` VarChar( 20 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
`reason` VarChar( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
PRIMARY KEY ( `SlNo` ) )
我们需要计算特定日期的总工作时间,为此我们需要减去具有值注销的 LogType 列和具有值登录的上一个 LogType 列。下面列出了一天的示例行。
SlNo UserName LogType LogTime
355034 Krishna LogIn 2018-04-01 08:02:32
355036 Krishna LogOut 2018-04-01 08:50:28
355041 Krishna LogIn 2018-04-01 09:19:31
355048 Krishna LogOut 2018-04-01 10:03:16
355051 Krishna LogIn 2018-04-01 10:12:12
355060 Krishna LogOut 2018-04-01 11:07:48
355064 Krishna LogIn 2018-04-01 11:20:01
355072 Krishna LogOut 2018-04-01 12:33:52
355073 Krishna LogIn 2018-04-01 12:35:19
355082 Krishna LogOut 2018-04-01 13:10:02
355083 Krishna LogIn 2018-04-01 13:14:07
355098 Krishna LogOut 2018-04-01 17:02:06
如何编写 sql 查询以获取当天的总工作时间
您可以使用子查询来获取与登录名关联的注销,并使用一些日期函数来汇总工作时间。下面的查询说明了它是如何工作的,如果你愿意,你可以重新调整它以使其更有效率。
drop table if exists t;
CREATE TABLE `t` (
`SlNo` Int( 11 ) AUTO_INCREMENT NOT NULL,
`UserName` VarChar( 20 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`LogType` VarChar( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`LogTime` DateTime NOT NULL,
PRIMARY KEY ( `SlNo` ) );
insert into t (username,logtype,logtime) values
( 'Krishna' , 'LogIn' , '2018-04-01 08:02:32'),
('Krishna' , 'LogOut' , '2018-04-01 08:50:28'),
('Krishna' , 'LogIn' , '2018-04-01 09:19:31'),
('Krishna' , 'LogOut' , '2018-04-01 10:03:16'),
( 'Krishna' , 'LogIn' , '2018-05-01 08:02:32'),
( 'Krishna' , 'LogOut' , '2018-05-01 08:50:28'),
( 'Krishna' , 'LogIn' , '2018-05-01 09:19:31'),
( 'Krishna' , 'LogOut' , '2018-05-01 10:03:16'),
( 'bob' , 'LogIn' , '2018-05-01 08:02:32'),
( 'bob' , 'LogOut' , '2018-05-01 08:50:28'),
( 'bob' , 'LogIn' , '2018-05-01 09:19:31'),
( 'bob' , 'LogOut' , '2018-05-01 15:03:16');
select username, dt, sec_to_time(sum(time_to_sec(hrsworked)))
from
(
select username, date(logtime) dt,logtime logintime,
(select t1.logtime from t t1 where t1.username = t.username and t1.logtime > t.logtime and logtype = 'logout' order by logtime limit 1) logout,
timediff(
(select t1.logtime from t t1 where t1.username = t.username and t1.logtime > t.logtime and logtype = 'logout' order by logtime limit 1) ,
logtime) hrsworked
from t
where logtype = 'login'
) s
group by username,dt
order by dt,username;
+----------+------------+------------------------------------------+
| username | dt | sec_to_time(sum(time_to_sec(hrsworked))) |
+----------+------------+------------------------------------------+
| Krishna | 2018-04-01 | 01:31:41 |
| bob | 2018-05-01 | 06:31:41 |
| Krishna | 2018-05-01 | 01:31:41 |
+----------+------------+------------------------------------------+
3 rows in set (0.00 sec)