[mysql] mysql 在update中实现子查询的方式

mysql update中实现子查询的方式

 

---测试代码---------------

drop table if exists tb;
create table IF NOT EXISTS tb (Name varchar(10),Time1 datetime,Time2 datetime,Time3 varchar(8),Time4 varchar(8) );
insert tb (name,time1,time2)
select '1','2010-08-04  05:06:26 ','2010-08-04  05:06:29 ' union all
select '1','2010-08-04  05:06:33 ','2010-08-04  09:53:32 ' union all
select '1','2010-08-04  06:06:26 ','2010-08-04  07:06:29 ' union all
select '1','2010-08-05  09:43:10 ','2010-08-05  12:43:50 ' union all
select '1','2010-08-05  05:43:56 ','2010-08-05  07:23:33 ' union all
select '1','2010-08-06  09:43:56 ','2010-08-06  14:55:59 ' union all
select '1','2010-08-07  09:04:56 ','2010-08-07  17:43:56 ' union all
select '1','2010-08-08  08:56:10 ',null;

update tb set time3=timediff(time2,time1);
select * from tb;

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;
select * from tb;

 

 

 

---以下为运行过程------------------------------

mysql> create table IF NOT EXISTS tb (Name varchar(10),Time1 datetime,Time2 date
time,Time3 varchar(8),Time4 varchar(8) );
Query OK, 0 rows affected (0.03 sec)

mysql> insert tb (name,time1,time2)
    -> select '1','2010-08-04  05:06:26 ','2010-08-04  05:06:29 ' union all
    -> select '1','2010-08-04  05:06:33 ','2010-08-04  09:53:32 ' union all
    -> select '1','2010-08-04  06:06:26 ','2010-08-04  07:06:29 ' union all
    -> select '1','2010-08-05  09:43:10 ','2010-08-05  12:43:50 ' union all
    -> select '1','2010-08-05  05:43:56 ','2010-08-05  07:23:33 ' union all
    -> select '1','2010-08-06  09:43:56 ','2010-08-06  14:55:59 ' union all
    -> select '1','2010-08-07  09:04:56 ','2010-08-07  17:43:56 ' union all
    -> select '1','2010-08-08  08:56:10 ',null;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql>
mysql> update tb set time3=timediff(time2,time1);
Query OK, 7 rows affected (0.00 sec)
Rows matched: 8  Changed: 7  Warnings: 0

mysql> 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-04 06:06:26 | 2010-08-04 07:06:29 | 01:00:03 | NULL  |
| 1    | 2010-08-05 09:43:10 | 2010-08-05 12:43:50 | 03:00:40 | NULL  |
| 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 14:55:59 | 05:12:03 | NULL  |
| 1    | 2010-08-07 09:04:56 | 2010-08-07 17:43:56 | 08:39:00 | NULL  |
| 1    | 2010-08-08 08:56:10 | NULL                | NULL     | NULL  |
+------+---------------------+---------------------+----------+-------+
8 rows in set (0.00 sec)

mysql>
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, 4 rows affected (0.00 sec)
Rows matched: 5  Changed: 4  Warnings: 0

mysql> 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-04 06:06:26 | 2010-08-04 07:06:29 | 01:00:03 | 05:47:05 |
| 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 14:55:59 | 05:12:03 | 05:12:03 |
| 1    | 2010-08-07 09:04:56 | 2010-08-07 17:43:56 | 08:39:00 | 08:39:00 |
| 1    | 2010-08-08 08:56:10 | NULL                | NULL     | NULL     |
+------+---------------------+---------------------+----------+----------+
8 rows in set (0.00 sec)

mysql>

 

总结：

      在写这段代码前，好了sql server的。但是转到mysql里始终不能成功，在不断的看帮助、查资料、测试后发现mysql的update的一些特点

 

1、update 时，更新的表不能在set和where中用于子查询；

2、update 时，可以对多个表进行更新（sqlserver不行）；

         如：update ta a,tb b set a.Bid=b.id ,b.Aid=a.id;  

3、update 后面可以做任意的查询，这个作用等同于from；