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作为完全连接的一部分可以创建空值。
