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

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

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_efrom (    select *,row_number() over (partition by col_a order by col_c) rown    from @t1) afull 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.rownorder by isnull(a.col_a,b.col_a),isnull(a.rown,b.rown)

使用row_number作为完全连接的一部分可以创建空值。