目的:
将db2.tb2中的最新数据更新到db1.tb1
两表以name字段做关联
db2.tb2只是记录表,name有大量重复
db1.tb1中的数据是最新,且name不能重复
db1.tb1
----------------------
id,name,age,count,time
----------------------
1,abc,1,40,2010-09-10 10:20
2,pig,2,50,2010-09-11 10:20
3,dog,3,60,2010-09-12 10:20
db2.tb2
-----------------------
id,name,age,count,time
-----------------------
1,abc,11,220,2010-09-09 05:20
2,abc,21,320,2010-09-13 07:20
3,abc,31,420,2010-09-08 11:20
4,pig,41,520,2010-09-10 09:20
5,pig,51,620,2010-09-13 06:20
6,dog,61,720,2010-09-15 03:20
7,dog,71,820,2010-07-10 12:20
结果需要是这样:
db1.tb1
----------------------
id,name,age,count,time
----------------------
1,abc,21,320,2010-09-13 07:20
2,pig,51,620,2010-09-13 06:20
3,dog,61,720,2010-09-15 03:20
将db2.tb2中的最新数据更新到db1.tb1
两表以name字段做关联
db2.tb2只是记录表,name有大量重复
db1.tb1中的数据是最新,且name不能重复
db1.tb1
----------------------
id,name,age,count,time
----------------------
1,abc,1,40,2010-09-10 10:20
2,pig,2,50,2010-09-11 10:20
3,dog,3,60,2010-09-12 10:20
db2.tb2
-----------------------
id,name,age,count,time
-----------------------
1,abc,11,220,2010-09-09 05:20
2,abc,21,320,2010-09-13 07:20
3,abc,31,420,2010-09-08 11:20
4,pig,41,520,2010-09-10 09:20
5,pig,51,620,2010-09-13 06:20
6,dog,61,720,2010-09-15 03:20
7,dog,71,820,2010-07-10 12:20
结果需要是这样:
db1.tb1
----------------------
id,name,age,count,time
----------------------
1,abc,21,320,2010-09-13 07:20
2,pig,51,620,2010-09-13 06:20
3,dog,61,720,2010-09-15 03:20
解决方案 »
- 如何用存储过程实现提取前N条记录或插入N条记录
- 一条执行时间超级长的SQL语句,急需高手优化!!!
- 向ACMAIN_CHM、左左等高手请教,如何设置MySQL Workbench?
- InnoDB 表空间
- 触发器 的 Before 和 After
- mysql6数据同步用触发器可行吗
- 这么正常的SQL都出错,大家,救救我吧!!!!!LEN语句!!100分!!这么一个SQL语句竟然出错,大家帮助!!
- mysql有没有像sql server2000一样的图形开发界面!
- 求助:xtrabackup 2.4.2 备份mysq-5.7
- SQL 根据1列数字 求其他两列相加
- sql 多条合并 进来都有分
- 如何优化下查询速度(有sql语句)
on a.name=b.name
set a.age=c.age,a.count=c.count,a.time=c.time
ts2 建立触发器 insert时插入 tb1
select a.* from db2 a inner join (
select name,max(time) as ma from db2 group by name) b
on a.name=b.name and a.time=b.ma) b1 on a.name=b1.name
set a1.age=b1.age,a1.count=b1.count,a1.time=b1.time
set a.age=k.age,a.count=k.count,a.time=k.time
where a.name=k.name ;
select a.* from db2 a inner join (
select name,max(time) as ma from db2 group by name) b
on a.name=b.name and a.time=b.ma) b1 on a1.name=b1.name
set a1.age=b1.age,a1.count=b1.count,a1.time=b1.time
Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='
show variables like 'char%';