时间统计

Question

临时表：
 CREATE TABLE `TimeInOutRecord` (
 `EmployeeName` varchar(40) default NULL,`InTime` datetime default NULL,`OutTime` datetime default NULL,
 `WorkTime` time default NULL,`DaybyWorkTime` time default NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `timeinoutrecord` (`EmployeeName`,`InTime`,`OutTime`,`WorkTime`,`DaybyWorkTime`) VALUES 
 ('CASHIER 2           ','2011-02-09 21:19:21','2011-02-10 07:20:32','10:01:11',NULL),
 ('CASHIER 2           ','2011-02-10 08:23:16','2011-02-10 09:01:31','00:38:15',NULL),
 ('CASHIER 2           ','2011-02-10 20:20:59','2011-02-10 20:24:00','00:03:01',NULL),
 ('CASHIER 2           ','2011-02-10 20:26:07',NULL,NULL,NULL); update timeinoutrecord a, (select SEC_TO_TIME(sum(TIME_TO_SEC(worktime))) col,max(inTime) time,employeename from timeinoutrecord  group by DATE_FORMAT(inTime, '%Y-%m-%d')) b Set a.daybyworktime = b.Col Where a.EmployeeName = b.EmployeeName And a.inTime = b.Time
这个时间按正常来说是没错的，不过我现在统计时间是按20：00：00-19：59：59来统计的，正确的是：'10:01:11'+'00:38:15'=‘10：39：26’ 和 '00:03:01'，怎么写UPDATE语句。

Answer 1

mysql> select * from timeinoutrecord;
+----------------------+---------------------+---------------------+----------+---------------+
| EmployeeName         | InTime              | OutTime             | WorkTime |DaybyWorkTime |
+----------------------+---------------------+---------------------+----------+---------------+
| CASHIER 2            | 2011-02-09 21:19:21 | 2011-02-10 07:20:32 | 10:01:11 |NULL          |
| CASHIER 2            | 2011-02-10 08:23:16 | 2011-02-10 09:01:31 | 00:38:15 |NULL          |
| CASHIER 2            | 2011-02-10 20:20:59 | 2011-02-10 20:24:00 | 00:03:01 |NULL          |
| CASHIER 2            | 2011-02-10 20:26:07 | NULL                | NULL     |NULL          |
+----------------------+---------------------+---------------------+----------+---------------+
4 rows in set (0.03 sec)mysql> update timeinoutrecord a,
    ->  (select SEC_TO_TIME(sum(TIME_TO_SEC(worktime))) col,max(inTime) time,employeename
    ->          from timeinoutrecord
    ->          group by DATE_FORMAT(inTime-interval 20 hour, '%Y-%m-%d')
    ->          ) b Set a.daybyworktime = b.Col Where a.EmployeeName = b.EmployeeName And a.inTime = b.Time;
Query OK, 2 rows affected (0.05 sec)
Rows matched: 2  Changed: 2  Warnings: 0
mysql> select * from timeinoutrecord;
+----------------------+---------------------+---------------------+----------+---------------+
| EmployeeName         | InTime              | OutTime             | WorkTime |DaybyWorkTime |
+----------------------+---------------------+---------------------+----------+---------------+
| CASHIER 2            | 2011-02-09 21:19:21 | 2011-02-10 07:20:32 | 10:01:11 |NULL          |
| CASHIER 2            | 2011-02-10 08:23:16 | 2011-02-10 09:01:31 | 00:38:15 |10:39:26      |
| CASHIER 2            | 2011-02-10 20:20:59 | 2011-02-10 20:24:00 | 00:03:01 |NULL          |
| CASHIER 2            | 2011-02-10 20:26:07 | NULL                | NULL     |00:03:01      |
+----------------------+---------------------+---------------------+----------+---------------+
4 rows in set (0.00 sec)mysql>

Answer 2

狼头兄 新年好！
inTime-interval 20 hour, '%Y-%m-%d'
是减20小时，只对这2组数据有效。有没有灵活一点的？