sql - 如何连接 SQL Server 中的多个列并在一个表中包含其他表中不存在的列？

Question

我有两个表，其架构与示例表 1 和示例表 2 相似。两个表都有用于标识两个表中的数据的列。A 列和 B 列在两个表中。

表格1：

Col A   | Col B | Col C | Col D  
--------+-------+-------+------
Cat 1   | Bla a | C-1   | D-1  
Cat 1   | Bla a | C-2   | D-2  
Cat 1   | Bla a | C-3   | D-3  
Cat 2   | Bla b | C-4   | D-4   
Cat 2   | Bla b | C-5   | D-5  

表 2：

Col A   | Col B | Col E  
--------+-------+------
Cat 1   | Bla a | E-1   
Cat 2   | Bla b | E-2   
Cat 2   | Bla b | E-3   
Cat 2   | Bla b | E-4

所需的输出表：

Col A   | Col B | Col C | Col D | Col E  
--------+-------+-------+-------+------
Cat 1   | Bla a | C-1   | D-1   | E-1   
Cat 1   | Bla a | C-2   | D-2   | NULL  
Cat 1   | Bla a | C-3   | D-3   | NULL  
Cat 2   | Bla b | C-4   | D-4   | E-2    
Cat 2   | Bla b | C-5   | D-5   | E-3   
Cat 2   | Bla b | NULL  | NULL  | E-4

我需要组合、合并、更新、循环或以某种方式找到产生单个表的方法。在这个单个表中，我需要示例表 1 独有的列中的值与示例表 1 中的公共列值对齐。我还需要示例表 2 独有的列中的值与示例表 2 中的常用列值对齐。

如果一个表的公共列的匹配值比另一个表多，则唯一值应该为空，因为该记录成为其他唯一值的占位符。

表 1 在 A 和 B 列中有 3 条记录，其值为 Cat 1 和 Bla a。表 2 在 A 和 B 列中有 1 条记录，其值为 Cat 1 和 Bla a E 列的空值或空白值。

在我尝试执行联接或完全外部联接时，我得到了示例错误输出表中显示的结果。

所需输出表不应包含重复任一表中唯一值的值的记录。这显示在示例错误输出表中。

示例错误输出：

Col A   | Col B | Col C | Col D | Col E  
--------+-------+-------+-------+------
Cat 1   | Bla a | C-1   | D-1   | E-1     
Cat 1   | Bla a | C-2   | D-2   | E-1    
Cat 1   | Bla a | C-3   | D-3   | E-1    
Cat 2   | Bla b | C-4   | D-4   | E-2  
Cat 2   | Bla b | C-4   | D-4   | E-3  
Cat 2   | Bla b | C-4   | D-4   | E-4  
Cat 2   | Bla b | C-5   | D-5   | E-2       
Cat 2   | Bla b | C-5   | D-5   | E-3  
Cat 2   | Bla b | C-5   | D-5   | E-4    

标签： sqlsql-server

Answer 1

declare @t1 table (
col_a varchar(5) null
,col_b varchar(5) null
,col_c varchar(5) null
,col_d varchar(5) null
)

declare @t2 table (
col_a varchar(5) null
,col_b varchar(5) null
,col_e varchar(5) null
)


insert into @t1 values 
('Cat 1','Bla a','C-1','D-1')
,('Cat 1','Bla a','C-2','D-2')
,('Cat 1','Bla a','C-3','D-3')
,('Cat 2','Bla b','C-4','D-4')
,('Cat 2','Bla b','C-5','D-5')

insert into @t2 values 
('Cat 1'   , 'Bla a' , 'E-1'   )
,('Cat 2'   , 'Bla b' , 'E-2  ' )
,('Cat 2'   , 'Bla b' , 'E-3'   )
,('Cat 2'   , 'Bla b' , 'E-4')



select isnull(a.col_a,b.col_a) col_a, isnull(a.col_b,b.col_b) col_b, a.col_c,a.col_d,b.col_e
from (
    select *,row_number() over (partition by col_a order by col_c) rown
    from @t1
) a
full outer join (
    select *,row_number() over (partition by col_a order by col_e) rown
    from @t2
) b
    on a.col_a = b.col_a
    and a.col_b = b.col_b
    and a.rown = b.rown
order by isnull(a.col_a,b.col_a),isnull(a.rown,b.rown)

使用 row_number 作为完全连接的一部分是允许创建空值的原因。