MYSQL中同一个数据库中的两个表中的数据怎样合并？(只需要合并某个字段。)
===================
AAA结构如下：
ID username post
1 JACKY 85
2 CANDY 95
===================
BBB结构如下：
ID username post
1 JACKY 33
2 CANDY 25
===================
我现在想把BBB中的post字段和AAA中的post字段合并(如合并后JACKY的post是118，candy的post是120，其他字段如ID和username保持不变)，我试过用phpmyadmin先导出AAA的post字段，然后导入到BBB的post字段，不成功，提示键值已存在之类的错误。
因为涉及到好几千个ID，希望给出SQL语句，因为我本人对mysql语句并不太了解，谢谢！如能解决，送上100分。
补充一下，数据结构没说明白，分别是：
1111(数据库)_11(表)_AAA(字段)
1111(数据库)_22(表)_BBB(字段)
最新回答
傲忘天下
2024-11-27 14:58:09
1、忽略表之间的关联关系
ALTER TABLE db2.dbo.table NOCHECK CONSTRAINT 关系名
2、--将没有重复的数据合并
insert into db2.dbo.table(field1,field2...) select field1,field2... from db1.dbo.table a where a.username not in (select username from db2.dbo.table)
3、将重复的数据写入临时表
select field1,field2... into 新的临时表 from db1.dbo.table a where a.username in (select username from db2.dbo.table)
最美的痕迹叫回忆
2024-11-27 14:23:51
update aaa
set post = (select sum_post from (select aaa.ID,(aaa.post+bbb.post) sum_post from aaa,bbb where aaa.ID=bbb.ID) t1 where t1.ID=a.ID)
where exists (select 1 from bbb where aaa.ID =bbb.ID);
2.直接查询显示的方法参见上楼;
3.新建ccc表:
create table ccc as( select id,username,sum(post) sum_post from
(select id,username,post from aaa
union all
select id,username,post from bbb)
group by id,username; )
拥我怀中
2024-11-27 12:20:19
update aaa LEFT JOIN bbb ON bbb.username =aaa.username set aaa.post=aaa.post+bbb.post.
或者 update bbb LEFT JOIN aaa ON aaa.username =bbb.username set bbb.post=aaa.post+bbb.post.
如果不是唯一字段的话 不能用username 作条件左连接了 如果ID是对应的用ID 左连接
update bbb LEFT JOIN aaa ON aaa.id =bbb.id set bbb.post=aaa.post+bbb.post.
放逐°
2024-11-27 11:06:28
create table CCC
(ID int not null primary key
username varchar(20) not null
post int not null)
--将AAA中的数据复制到CCC里
declare @id int,@name varchar(20),@post int
declare yb cursor for
select id,username,post from AAA
yb open
fetch yb into @id,@name,@post
while @@fetch_status=0
begin
set identity_insert CCC on
inser into CCC(id,username,post) values(@id,@name,@post)
fetch yb into @id,@name,@post
end
close yb
deallocate yb
--CCC与BBB求和并更新到CCC表中
declare @sum int,@id1 int
declare yb1 cursor for
select B.id,B.post+C.post from BBB B join CCC C on B.id=C.id
yb1 open
fetch yb1 into @id1,@sum
while @@fetch_status=0
begin
set identity_insert CCC on
inser into CCC(post) values(@sum) where id=@id1
fetch yb into @sum
end
close yb1
deallocate yb1
理想三旬
2024-11-27 14:40:53
select aaa.username ,(aaa.post+bbb.post) as post
from _aaa表 , _bbb表
where aaa.username=bbb.username
热门标签
