CREATE TABLE a1(
f1 int Null,
f2 int Null,
f3 int Null
)
insert into a1 select 0,1,0
union all select 0,2,0
union all select 0,3,0CREATE TABLE a2(
f1 int Null,
f2 int Null,
f3 int Null
)
insert into a2 select 7,12,4
union all select 8,2,5
union all select 9,19,6select * from a1
union select * from a2
drop table a1
drop table a2结果:
f1 f2 f3
0 1 0
0 2 0
0 3 0
7 12 4
8 2 5
9 19 6需要结果:
f1 f2 f3
0 1 0
0 3 0
7 12 4
8 2 5
9 19 6即合并的结果中去掉表一中f2与表二f2相同的纪录
f1 int Null,
f2 int Null,
f3 int Null
)
insert into a1 select 0,1,0
union all select 0,2,0
union all select 0,3,0CREATE TABLE a2(
f1 int Null,
f2 int Null,
f3 int Null
)
insert into a2 select 7,12,4
union all select 8,2,5
union all select 9,19,6select * from a1
union select * from a2
drop table a1
drop table a2结果:
f1 f2 f3
0 1 0
0 2 0
0 3 0
7 12 4
8 2 5
9 19 6需要结果:
f1 f2 f3
0 1 0
0 3 0
7 12 4
8 2 5
9 19 6即合并的结果中去掉表一中f2与表二f2相同的纪录
union select * from a2
union select * from a2)a
union select * from a2
是否可以将结果保存在a2呢?
------------------------------------------
那a2不是又有重复的记录了?
先删除a2,再插入
truncate table a2
insert into a2
select * from a1 where not exists(select 1 from a2 where a1.f2=a2.f2)
union select * from a2
应该就是:向a2插入在a1中没有的记录(a1.f2=a2.f2)
insert into a2
select * from a1 where not exists(select 1 from a2 where a1.f2=a2.f2)