求实际表合并的SQL语句
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,0
CREATE 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,6
select * 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相同的纪录
[解决办法]
绕了一个大弯.
应该就是:向a2插入在a1中没有的记录(a1.f2=a2.f2)
insert into a2
select * from a1 where not exists(select 1 from a2 where a1.f2=a2.f2)