寻找在sql Server中设置“差异”的简单/可扩展方式,请参阅下文.
如果你无法从图片中看出我正在寻找那些不在交叉路口的东西.
我见过一种方法：
select * from (
(select 'test1' as a,1 as b)
union all
(select 'test2' as a,2 as b union all select 'test1' as a,1 as b )
)un group by a,b having count(1)=1
但我担心如果我使用两个大集合会发生什么(我不会从select”常量语句中查询,我的查询将从实际表中提取.)
编辑：
可能解决方案
drop table #temp_a;
drop table #temp_b;
go
select * into #temp_a from (
select 1 as num,'String' as two,'int'as three,'purple' as four union all
select 2 as num,'dog' as two,'purple' as four union all
select 3 as num,'cat' as four ) a
select * into #temp_b from (
select 1 as num,'decimal'as three,'purple' as four union all
select 2 as num,'purple' as four union all
select 3 as num,'dog' as four ) b
SELECT IsNull(a.num,b.num) A,IsNull(a.two,b.two) B,IsNull(a.three,b.three) C,IsNull(a.four,b.four) D
FROM #temp_a a
FULL OUTER JOIN #temp_b b ON (a.num=b.num AND a.two=b.two and a.three=b.three and a.four=b.four)
WHERE (a.num is null or b.num is null )
结果：
1 String int purple
3 dog int cat
1 String dec purple
3 dog int dog
解决方法
这样的事怎么样？
SELECT A,B FROM Table1 EXCEPT SELECT A,B FROM Table2 UNION SELECT A,B FROM Table2 EXCEPT SELECT A,B FROM Table1
以下是FULL OUTER JOIN方法的示例(假设A在两个表中都不可为空)
SELECT IsNull(Table1.A,Table2.A) a,IsNull(Table1.B,Table2.B) B FROM Table1 FULL OUTER JOIN Table2 ON (Table1.A=Table2.A AND Table1.B=Table2.B) WHERE Table1.A is null or Table2.A is null
