细胞与细胞在SQL服务器比较呢？(Cell to Cell comparison in Sql Se

我有tbl1 ：

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

我有tbl2 ：

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

我需要从每个细胞比较tbl1中的适当位置tbl2 ：

所期望的输出是：

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

视觉表现 ：

我试过很多疑问......但没有成功...

Answer 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

Answer 2:

首先，你应该UNPIVOT数据：

   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

然后，你可以比较的数据：

   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

免责声明： - 未测试。

编辑过 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

Answer 3:

做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)

文章来源: Cell to Cell comparison in Sql Server?