Sql服务器中的单元对单元的比较?

I have tbl1 :

我有tbl1:

   Id  | c1  |  c2  |  c3   |
   ____|_____|______|_______|_
    1     a     b       c 
   ____|_____|______|_______|_
    2     h     j       k
   ____|_____|______|_______|_
    3     t     y       u
   ____|_____|______|_______|_

I have tbl2 :

我有tbl2:

   Id  | c1  |  c2  |  c3   |
   ____|_____|______|_______|_
    1     a     b       D 
   ____|_____|______|_______|_
    2     c     c       c
   ____|_____|______|_______|_
    3     k     l       k
   ____|_____|______|_______|_

I need to compare each cell from tbl1 to its appropriate place in tbl2 :

我需要将tbl1中的每个单元格与tbl2中的相应位置进行比较:

the desired output is :

期望输出为:

   Id  |tbl1 | tbl2 | 
   ____|_____|______| 
    1     a     a     
   ____|_____|______| 
    1     b     b      
   ____|_____|______| 
    1     c     d      
   ____|_____|______| 
    2     h     c     
   ____|_____|______| 
    2     j     c      
   ____|_____|______| 
    2     k     c      
   ____|_____|______| 
          ...
          ...
          ...
          ...

visual representation :

可视化表示:

i tried many queries... but not succeed...

我尝试了许多查询…但是没有成功…

3 个解决方案

#1

select T1.id, T1.tbl1, T2.tbl2
from (
       select U.id, U.tbl1, U.col
       from tbl1
         unpivot (tbl1 for col in (c1, c2, c3)) U
     ) T1
  inner join 
     (
       select U.id, U.tbl2, U.Col
       from tbl2
         unpivot (tbl2 for col in (c1, c2, c3)) U
     ) T2
    on T1.id = T2.id and
       T1.col = T2.col
order by T1.id

#2

First you should unpivot data:

首先，您应该取消数据透视:

   select Id, C1, 'C1' as C from tbl1 union all
   select Id, C2, 'C2' as C from tbl1 union all
   select Id, C3, 'C2' as C from tbl1 union all

Then you can compare data:

然后你可以比较数据:

   select coalesce( uTbl1.Id,uTbl2.Id) as Id, uTbl1.C, uTbl2.C
   from (
     select Id, C1 as C, 'C1' as T from tbl1 union all
     select Id, C2 as C, 'C2' as T from tbl1 union all
     select Id, C3 as C, 'C3' as T from tbl1 ) uTbl1
   full outer join (
     select Id, C1 as C, 'C1' as T from tbl2 union all
     select Id, C2 as C, 'C2' as T from tbl2 union all
     select Id, C3 as C, 'C3' as T from tbl2 ) uTbl2
        on uTbl1.Id = uTbl2.Id and  uTbl1.T = uTbl2.T

Disclaimer: - Not tested.

免责声明:——而不是测试。

Edited With CTE:

CTE编辑:

   ; with 
    uTbl1 as (
     select Id, C1 as C, 'C1' as T from tbl1 union all
     select Id, C2 as C, 'C2' as T from tbl1 union all
     select Id, C3 as C, 'C3' as T from tbl1 ) 
   ,uTbl2 as (
     select Id, C1 as C, 'C1' as T from tbl2 union all
     select Id, C2 as C, 'C2' as T from tbl2 union all
     select Id, C3 as C, 'C3' as T from tbl2 ) 
   select coalesce( uTbl1.Id,uTbl2.Id) as Id, uTbl1.C, uTbl2.C
   from 
      uTbl1
   full outer join 
      uTbl2
         on uTbl1.Id = uTbl2.Id and  uTbl1.T = uTbl2.T

#3

Do 3 atomic subqueries and use then with UNION ALL to get the final result:

进行3个原子子查询，然后使用UNION ALL获得最终结果:

SELECT tbl1.id, tbl1.c1, tbl2.c1 FROM tbl1
INNER JOIN tbl2 on tbl1.id = tbl2.id

UNION ALL

SELECT tbl1.id, tbl1.c2, tbl2.c2 FROM tbl1
INNER JOIN tbl2 on tbl1.id = tbl2.id

UNION ALL

SELECT tbl1.id, tbl1.c3, tbl2.c3 FROM tbl1
INNER JOIN tbl2 on tbl1.id = tbl2.id

ORDER BY 1  --sort by column 1 (the IDs)