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

（编辑：湖南网）

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