有2个表,table1和table2,其中vcode代表信息编号,第二个表中的vcode与第一个表中的vcode关联的内容表:
create table1(
vcode varchar(30),
content varchar(100),
codetype varchar(10),
operatortype int,
primary key (vcode)
)内容:
vcode content codetype operatortype
1111 content1 1 0
1112 content2 2 0
1113 content3 1 0
1114 content4 1 0操作表:
create table2(
id int unsigned not null auto_increment primary key,
vcode varchar(30),
operatortype int,
operatortime datetime
)内容:
id vcode operatortype operatortime
1 1111 1 2012-10-10 09:23:22
2 1111 2 2012-10-10 10:00:11
3 1111 3 2012-11-01 12:23:20
4 1112 1 2012-10-02 23:22:10
5 1112 2 2012-10-16 19:20:20
6 1113 1 2012-10-04 11:11:12
7 1113 2 2012-10-06 12:12:11
8 1113 4 2012-10-22 19:50:42
9 1114 2 2012-10-03 09:03:24
10 1114 3 2012-10-12 18:08:10
以上只是一些简单数据,实际会有很多条数据
我现在要将table1中codetype=1的vcode的operatortype值刷新为table2中相同vcode记录的按时间排序最后一项的operatortype为3的opertortype值本人刚接触mysql不久,感觉这个太复杂了,在这里求救一下,希望高手指点,谢谢
create table1(
vcode varchar(30),
content varchar(100),
codetype varchar(10),
operatortype int,
primary key (vcode)
)内容:
vcode content codetype operatortype
1111 content1 1 0
1112 content2 2 0
1113 content3 1 0
1114 content4 1 0操作表:
create table2(
id int unsigned not null auto_increment primary key,
vcode varchar(30),
operatortype int,
operatortime datetime
)内容:
id vcode operatortype operatortime
1 1111 1 2012-10-10 09:23:22
2 1111 2 2012-10-10 10:00:11
3 1111 3 2012-11-01 12:23:20
4 1112 1 2012-10-02 23:22:10
5 1112 2 2012-10-16 19:20:20
6 1113 1 2012-10-04 11:11:12
7 1113 2 2012-10-06 12:12:11
8 1113 4 2012-10-22 19:50:42
9 1114 2 2012-10-03 09:03:24
10 1114 3 2012-10-12 18:08:10
以上只是一些简单数据,实际会有很多条数据
我现在要将table1中codetype=1的vcode的operatortype值刷新为table2中相同vcode记录的按时间排序最后一项的operatortype为3的opertortype值本人刚接触mysql不久,感觉这个太复杂了,在这里求救一下,希望高手指点,谢谢
解决方案 »
- 如何设置mysql 指定数据库为只读
- mysql无法启动 [ERROR] /usr/local/mysql/libexec/mysqld: unknown variable 'uesr=mysql'
- Show Index 与 information_schema.statistics
- 如何设置表中AUTO_INCREMENT的初始值为0
- 如何判断表存在?不存在则创建表?
- 如果将ORACLE中含BLOB和CLOB字段的表的数据导到MYSQL中?
- MySQL 优化问题 ?
- MySQL/Postgresql是开放源代码的吗?在哪儿可以下载到他们的源代码?可以对他们的源代码进行修改吗?
- 请教mysql的时间设置
- 依旧解决不了的Mysql 1130错误
- 如何锁定所有表,或整个库?
- help
ON a.vcode=b.vcode AND b.operatortype=3
SET a.operatortype=b.operatortype
WHERE a.codetype='1'
如果最大时间的 operatortype 也是最大的数字的话,用这个就可以了:
update table1 as t1 left join (select vcode, max(operatortype) as operatortype from table2 group by vcode) as t2 on t1.vcode = t2.vcode set t1.operatortype = t2.operatortype;
mysql> select * from table1;
+-------+----------+----------+--------------+
| vcode | content | codetype | operatortype |
+-------+----------+----------+--------------+
| 1111 | content1 | 1 | 0 |
| 1112 | content2 | 2 | 0 |
| 1113 | content3 | 1 | 0 |
| 1114 | content4 | 1 | 0 |
+-------+----------+----------+--------------+
4 rows in set (0.00 sec)mysql> update table1 as t1
-> left join
-> (
-> select *
-> from table2
-> where (vcode, operatortime) in (
-> select vcode, max(operatortime)
-> from table2
-> group by vcode
-> )
-> ) as t2
-> on t1.vcode = t2.vcode
-> set t1.operatortype = t2.operatortype;
Query OK, 4 rows affected (0.03 sec)
Rows matched: 4 Changed: 4 Warnings: 0mysql> select * from table1;
+-------+----------+----------+--------------+
| vcode | content | codetype | operatortype |
+-------+----------+----------+--------------+
| 1111 | content1 | 1 | 3 |
| 1112 | content2 | 2 | 2 |
| 1113 | content3 | 1 | 4 |
| 1114 | content4 | 1 | 3 |
+-------+----------+----------+--------------+
4 rows in set (0.00 sec)mysql>
INNER JOIN
(SELECT *
FROM table2 t2
WHERE NOT EXISTS (
SELECT 1
FROM table2 t22
WHERE t2.vcode=t22.vcode AND t2.operatortime<t22.operatortime)) tmp
ON t1.vcode=tmp.vcode
SET t1.operatortype=tmp.operatortype;
这个就清楚了,那不就是我4楼的加上 codetype=1 这个条件嘛。