sql-server – SQL Server差异(与交叉相反)

寻找在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

（编辑：上饶站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!