select a.* from a1 a Full Outer Join a2 b on a.field1=b.field1 and a.field2=b.field2 where b.field1 is null or a.field1 is null
适用于没有PRIMARY KEY的情况: create table test1(field1 varchar(4),field2 varchar(4)) go create table test2(field1 varchar(4),field2 varchar(4)) go insert into test1 select 'aa','a' union select 'bb','c' union select 'cc','d'insert into test2 select 'ab','a' union select 'aa','c' union select 'cc','d'select * into #temp from test_yb1 go insert into #temp select * from test_yb2 go select distinct * into table_name from #temp go drop table #temp
有PRIMARY KEY 的情况: select c.* from test1 c,test2 d where c.field1<>d.field1 and c.field2<>d.field2 union select d.* from test1 c,test2 d where c.field1<>d.field1 or c.field2<>d.field2field1 field2 ------ ------ aa a aa c ab a bb c cc d(所影响的行数为 5 行)结果应该是没问题,楼主测试一下!!!行了别忘记给分啊嘿嘿
from a1 a Full Outer Join a2 b on a.field1=b.field1 and a.field2=b.field2
where b.field1 is null or a.field1 is null
create table test1(field1 varchar(4),field2 varchar(4))
go create table test2(field1 varchar(4),field2 varchar(4))
go insert into test1 select 'aa','a'
union select 'bb','c'
union select 'cc','d'insert into test2 select 'ab','a'
union select 'aa','c'
union select 'cc','d'select * into #temp from test_yb1
go
insert into #temp select * from test_yb2
go
select distinct * into table_name from #temp
go
drop table #temp
select c.* from test1 c,test2 d where c.field1<>d.field1 and c.field2<>d.field2
union
select d.* from test1 c,test2 d where c.field1<>d.field1 or c.field2<>d.field2field1 field2
------ ------
aa a
aa c
ab a
bb c
cc d(所影响的行数为 5 行)结果应该是没问题,楼主测试一下!!!行了别忘记给分啊嘿嘿