--> 测试数据: @tt declare @tt table (a1 varchar(2),a2 varchar(1),a3 varchar(1),a4 varchar(1),a5 varchar(1)) insert into @tt select '1q','a','b','c','d' union all select '2q','b','a','c','a' union all select '3q','c','c','g','a' --> 测试数据: @tt2 declare @tt2 table (b1 varchar(2),b2 varchar(2),b3 varchar(2),b4 varchar(2)) insert into @tt2 select '1r','1q','2q','2q'select a.b1,a.b2,b.a2,b.a3,b.a4,b.a5,a.b3,c.a2,c.a3,c.a4,c.a5,a.b4,d.a2,d.a3,d.a4,d.a5 from @tt2 a left join @tt b on a.b2=b.a1 left join @tt c on a.b3=c.a1 left join @tt d on a.b4=d.a1--结果:b1 b2 a2 a3 a4 a5 b3 a2 a3 a4 a5 b4 a2 a3 a4 a5 ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- 1r 1q a b c d 2q b a c a 2q b a c a
--> 测试数据: @tt
declare @tt table (a1 varchar(2),a2 varchar(1),a3 varchar(1),a4 varchar(1),a5 varchar(1))
insert into @tt
select '1q','a','b','c','d' union all
select '2q','b','a','c','a' union all
select '3q','c','c','g','a'
--> 测试数据: @tt2
declare @tt2 table (b1 varchar(2),b2 varchar(2),b3 varchar(2),b4 varchar(2))
insert into @tt2
select '1r','1q','2q','2q'select a.b1,a.b2,b.a2,b.a3,b.a4,b.a5,a.b3,c.a2,c.a3,c.a4,c.a5,a.b4,d.a2,d.a3,d.a4,d.a5 from @tt2 a
left join @tt b on a.b2=b.a1
left join @tt c on a.b3=c.a1
left join @tt d on a.b4=d.a1--结果:b1 b2 a2 a3 a4 a5 b3 a2 a3 a4 a5 b4 a2 a3 a4 a5
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
1r 1q a b c d 2q b a c a 2q b a c a