SQL语句 永生进

Question

timeinoutrecord表的结构是：
name intime outtime worktime daybyworktime
time4是临时字段，select
case   when not exists(select 1 from timeinoutrecord
where datediff(a.inTime,inTime)=0 and a.inTime<inTime)
then (select SEC_TO_TIME(sum(TIME_TO_SEC(worktime)))
from timeinoutrecord where datediff(a.inTime,inTime)=0)
else '' end as time4
from timeinoutrecord a
查询结果：time404:47:0204:40:1700:51:58
08:39:00
然后更新timeinoutrecord的值。

Answer 1

update a set daybyworktime=
(case   when not exists(select 1 from timeinoutrecord
where datediff(a.inTime,inTime)=0 and a.inTime<inTime)
then (select SEC_TO_TIME(sum(TIME_TO_SEC(worktime)))
from timeinoutrecord where datediff(a.inTime,inTime)=0)
else '' end )
from timeinoutrecord a
试试

Answer 2

支持！update a set a.field = b.field1 from a join b on a.id = b.id where ...

Answer 3

回永生：
不可以
 是不是由于Time4是临时字段，所以无法更新到daybyworktime?

Answer 4

update timeinoutrecord a,
(select SEC_TO_TIME(sum(TIME_TO_SEC(worktime))) col,max(inTime) time,name
from timeinoutrecord group by DATE_FORMAT(inTime, '%Y-%m-%d')) b
set a.daybyworktime=b.col
where a.name=b.name and a.inTime=b.time;这个差不多了

Answer 5

mysql> update tb a,
    -> (select SEC_TO_TIME(sum(TIME_TO_SEC(time3))) col,max(time1) time,name
    -> from tb group by DATE_FORMAT(time1, '%Y-%m-%d'))b
    -> set time4=b.col
    -> where a.name=b.name and a.time1=b.time;
Query OK, 5 rows affected, 2 warnings (0.02 sec)
Rows matched: 5  Changed: 5  Warnings: 2mysql> select * from tb;
+------+---------------------+---------------------+----------+----------+
| Name | Time1               | Time2               | Time3    | Time4    |
+------+---------------------+---------------------+----------+----------+
| 1    | 2010-08-04 05:06:26 | 2010-08-04 05:06:29 | 00:00:03 | NULL     |
| 1    | 2010-08-04 05:06:33 | 2010-08-04 09:53:32 | 04:46:59 | NULL     |
| 1    | 2010-08-05 09:43:10 | 2010-08-05 12:43:50 | 03:00:40 | 04:40:17 |
| 1    | 2010-08-05 05:43:56 | 2010-08-05 07:23:33 | 01:39:37 | NULL     |
| 1    | 2010-08-06 09:43:56 | 2010-08-06 02:55:59 | -06:47:5 | -06:47:0 |
| 1    | 2010-08-07 09:04:56 | 2010-08-07 05:43:56 | -03:21:0 | -03:21:0 |
| 1    | 2010-08-08 08:56:10 | NULL                | NULL     | NULL     |
| 1    | 2010-08-04 06:06:26 | 2010-08-04 07:06:29 | 01:00:03 | 05:47:05 |
+------+---------------------+---------------------+----------+----------+
8 rows in set (0.00 sec)mysql>