SQL Server 差异(与相交相反) [英] SQL Server Difference (opposite of intersect)
问题描述
寻找最简单/最具可扩展性的方式来做一组差异";在 SQL Server 中,请参见下文.
Looking for the easist/most scalable way to do a set "difference" in SQL Server see below.
如果你不能从图片中看出我正在寻找所有不在十字路口的东西.
If you can't tell from the picture i am looking for everything that is not in the intersection.
我见过一种方法:
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 '' 常量语句中查询,我的查询将从真实表中提取.)
But i fear what would happen if i used two large sets (i will not be querying from select '' constant statements, my queries will be pulling from real tables.)
可能的解决方案...
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, 'int'as three, 'purple' as four union all
select 3 as num, 'dog' as two, 'int'as three, 'cat' as four ) a
select * into #temp_b from (
select 1 as num, 'String' as two, 'decimal'as three, 'purple' as four union all
select 2 as num, 'dog' as two, 'int'as three, 'purple' as four union all
select 3 as num, 'dog' as two, 'int'as three, '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
1 String int purple
3 dog int cat
3 dog int cat
1 个字符串 dec 紫色
1 String dec purple
3 dog int dog
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 在两个表中都不能为空)
Here is an example with the FULL OUTER JOIN method (assuming A is not nullable in both tables)
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
这篇关于SQL Server 差异(与相交相反)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!