--tryselect x,y from tablea a where not exists(select 1 from tableb where a.x=x and a.y=y) union all select x,y from tableb b where not exists(select 1 from tablea where b.x=x and b.y=y)--------------------------select isnull(a.x,b.x),isnull(a.y,b.y) from tablea a full join tableb b on a.x=b.x where a.x is null or b.x is null
select * from A where x not in(select x from B where x is not null) union all select * from B where x not in(select x from A where x is not null)
继续加难A中有B中无,加标志0 B中有A中无, 加标志1 A和B不一致(x字段一样,y不同), 加标志2这样估计很难办了, 也是我真正需要实现的特别说明一下, 我指得加标志后select出来的效果应该是这样x, y, 0 x, y, 1 x, y, 2
--try 以后一次说清楚 select isnull(a.x,b.x) as X,isnull(a.y,b.y) as Y,case when b.x is null then 0 else 1 end as Flg from tablea a full join tableb b on a.x=b.x where a.x is null or b.x is null union all select a.x,a.y,2 from tablea a inner join tableb b on a.x=b.x and a.y<>b.y union all select b.x,b.y,2 from tablea a inner join tableb b on a.x=b.x and a.y<>b.y
select * from a full outer join b where a.x = b.x and ( a.x is null or b.x isnull)
from tablea a
where not exists(select 1 from tableb where a.x=x and a.y=y)
union all
select x,y
from tableb b
where not exists(select 1 from tablea where b.x=x and b.y=y)--------------------------select
isnull(a.x,b.x),isnull(a.y,b.y)
from tablea a full join tableb b
on a.x=b.x
where a.x is null or b.x is null
union all
select * from B where x not in(select x from A where x is not null)
B中有A中无, 加标志1
A和B不一致(x字段一样,y不同), 加标志2这样估计很难办了, 也是我真正需要实现的特别说明一下, 我指得加标志后select出来的效果应该是这样x, y, 0
x, y, 1
x, y, 2
select
isnull(a.x,b.x) as X,isnull(a.y,b.y) as Y,case when b.x is null then 0 else 1 end as Flg
from tablea a full join tableb b
on a.x=b.x
where a.x is null or b.x is null
union all
select a.x,a.y,2
from tablea a inner join tableb b
on a.x=b.x and a.y<>b.y
union all
select b.x,b.y,2
from tablea a inner join tableb b
on a.x=b.x and a.y<>b.y
full outer join b
where a.x = b.x
and ( a.x is null or b.x isnull)