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)