--测试 create table #a( a int, b int ) create table #b( a int, b int ) insert into #a select 1,1 insert into #a select 2,1 insert into #a select 3,1 insert into #a select 4,1 insert into #a select 1,2 insert into #a select 2,2 insert into #a select 3,2insert into #b select 1,1 insert into #b select 2,1 insert into #b select 3,1 insert into #b select 1,1 insert into #b select 5,1 --交集 select a,b from ( select distinct a,b from #a union all select distinct a,b from #b ) x group by a,b having count(*)>1 --A表减交集 这个应该就是你所要的结果 select a,b from ( select a,b from ( select distinct a,b from #a union all select distinct a,b from #b ) x group by a,b having count(*)=1 union all select distinct a,b from #a ) y group by a,b having count(*)>1 --B表减交集 select a,b from ( select a,b from ( select distinct a,b from #a union all select distinct a,b from #b ) x group by a,b having count(*)=1 union all select distinct a,b from #b ) y group by a,b having count(*)>1 drop table #a drop table #b
select distinct bc.a from (select a from b union all select a from c) bc看 這樣可以嗎?
create table #a(
a int,
b int
)
create table #b(
a int,
b int
)
insert into #a select 1,1
insert into #a select 2,1
insert into #a select 3,1
insert into #a select 4,1
insert into #a select 1,2
insert into #a select 2,2
insert into #a select 3,2insert into #b select 1,1
insert into #b select 2,1
insert into #b select 3,1
insert into #b select 1,1
insert into #b select 5,1
--交集
select a,b from (
select distinct a,b from #a
union all
select distinct a,b from #b
) x group by a,b having count(*)>1
--A表减交集 这个应该就是你所要的结果
select a,b from (
select a,b from (
select distinct a,b from #a
union all
select distinct a,b from #b
) x group by a,b having count(*)=1
union all
select distinct a,b from #a
) y group by a,b having count(*)>1
--B表减交集
select a,b from (
select a,b from (
select distinct a,b from #a
union all
select distinct a,b from #b
) x group by a,b having count(*)=1
union all
select distinct a,b from #b
) y group by a,b having count(*)>1
drop table #a
drop table #b
from (select a from b
union all
select a from c) bc看 這樣可以嗎?