declare @a table(a varchar(10),b varchar(10)) insert @a select 'A', 'A1' union all select 'A' ,'A2' union all select 'B' ,'B1' union all select 'B' ,'B2'declare @b table(a varchar(10),b varchar(10)) insert @b select 'A', 'A_D' union all select 'A1', 'A1_D' union all select 'A2', 'A2_D' union all select 'B', 'B_D' union all select 'B1', 'B1_D' union all select 'B2', 'B2_D'select a.a,b.b,a.b,c.b from @a a left join @b b on a.a=b.a left join @b c on a.b=c.a
create table t1(c11 varchar(10),c12 varchar(10)) insert into t1 values('A', 'A1') insert into t1 values('A', 'A2') insert into t1 values('B', 'B1') insert into t1 values('B', 'B2') create table t2(c21 varchar(10),c22 varchar(10)) insert into t2 values('A' , 'A_D') insert into t2 values('A1', 'A1_D') insert into t2 values('A2', 'A2_D') insert into t2 values('B' , 'B_D') insert into t2 values('B1', 'B1_D') insert into t2 values('B2', 'B2_D') select t3.c11,t3.c22,t4.* from ( select t1.*,t2.c22 from t1 , t2 where t1.c11 = t2.c21 ) t3 left join ( select t2.c21,t2.c22 from t1 , t2 where t1.c12 = t2.c21 ) t4 on t3.c12 = t4.c21drop table t1,t2/* c11 c22 c21 c22 ---------- ---------- ---------- ---------- A A_D A1 A1_D A A_D A2 A2_D B B_D B1 B1_D B B_D B2 B2_D(所影响的行数为 4 行) */
create table t1(c1 varchar(10),c2 varchar(10)) insert into t1 values('A', 'A1') insert into t1 values('A', 'A2') insert into t1 values('B', 'B1') insert into t1 values('B', 'B2') create table t2(c1 varchar(10),c2 varchar(10)) insert into t2 values('A' , 'A_D') insert into t2 values('A1', 'A1_D') insert into t2 values('A2', 'A2_D') insert into t2 values('B' , 'B_D') insert into t2 values('B1', 'B1_D') insert into t2 values('B2', 'B2_D')select t1.c1,t2.c2,t1.c2,t2.c2 from t1 left join t2 on t1.c1 = t2.c1 left join t2 t3 on t1.c2 = t3.c1drop table t1,t2/* c1 c2 c2 c2 ---------- ---------- ---------- ---------- A A_D A1 A_D A A_D A2 A_D B B_D B1 B_D B B_D B2 B_D(所影响的行数为 4 行) */
Select A.a, B.b, A.b, C.b, From T1 As A Left Join T2 As B On A.a=B.a Left Join T2 As C On A.b=C.a
/*抱歉有笔误改下*/ ---创建测试环境 Create Table T1(a varchar(10),b varchar(10)) Insert T1 Select 'A', 'A1' Union All Select 'A' ,'A2' Union All Select 'B' ,'B1' Union All Select 'B' ,'B2'Create Table T2(a varchar(10),b varchar(10)) Insert T2 Select 'A', 'A_D' Union All Select 'A1', 'A1_D' Union All Select 'A2', 'A2_D' Union All Select 'B', 'B_D' Union All Select 'B1', 'B1_D' Union All Select 'B2', 'B2_D' ---查询结果 Select A.a, B.b, A.b, C.b From T1 As A Left Join T2 As B On A.a=B.a Left Join T2 As C On A.b=C.a ---删除测试环境 Drop Table T1,T2 ---结果 /* a b b b ---------- ---------- ---------- ---------- A A_D A1 A1_D A A_D A2 A2_D B B_D B1 B1_D B B_D B2 B2_D(所影响的行数为 4 行) */
create table t1(c11 varchar(10),c12 varchar(10)) insert into t1 values('A', 'A1') insert into t1 values('A', 'A2') insert into t1 values('B', 'B1') insert into t1 values('B', 'B2') create table t2(c21 varchar(10),c22 varchar(10)) insert into t2 values('A' , 'A_D') insert into t2 values('A1', 'A1_D') insert into t2 values('A2', 'A2_D') insert into t2 values('B' , 'B_D') insert into t2 values('B1', 'B1_D') insert into t2 values('B2', 'B2_D') select a.c11,b.c22,a.c12,c.c22 from t1 a left join t2 b on a.c11=b.c21 left join t2 c on a.c12=c.c21 c11 c22 c12 c22 ---------- ---------- ---------- ---------- A A_D A1 A1_D A A_D A2 A2_D B B_D B1 B1_D B B_D B2 B2_D(4 row(s) affected)
select a.c1,b.c2 ,a.c2,c.c2 from t1 a inner join t2 b on a.c1=b.c1 inner join t2 c on c.c1=a.c2结果: c1 c2 c2 c2 ---------- ---------- ---------- ---------- A A_D A1 A1_D A A_D A2 A2_D B B_D B1 B1_D B B_D B2 B2_D
create table alphaA(a varchar(10),b varchar(10)) insert alphaA select 'A', 'A1' union select 'A','A2' union select 'B','B1' union select 'B','B2'create table alphaB(a varchar(10),b varchar(10)) insert alphaB select 'A', 'A_D' union select 'A1', 'A1_D' union select 'A2', 'A2_D' union select 'B', 'B_D' union select 'B1', 'B1_D' union select 'B2', 'B2_D'select a.a,b.b,a.b,c.b from alphaA a left join alphaB b on a.a=b.a left join alphaB c on a.b=c.a 结果: a b b b ---------- ---------- ---------- ---------- A A_D A1 A1_D A A_D A2 A2_D B B_D B1 B1_D B B_D B2 B2_D
insert @a select 'A', 'A1'
union all select 'A' ,'A2'
union all select 'B' ,'B1'
union all select 'B' ,'B2'declare @b table(a varchar(10),b varchar(10))
insert @b select 'A', 'A_D'
union all select 'A1', 'A1_D'
union all select 'A2', 'A2_D'
union all select 'B', 'B_D'
union all select 'B1', 'B1_D'
union all select 'B2', 'B2_D'select a.a,b.b,a.b,c.b from @a a left join @b b on a.a=b.a left join @b c on a.b=c.a
insert into t1 values('A', 'A1')
insert into t1 values('A', 'A2')
insert into t1 values('B', 'B1')
insert into t1 values('B', 'B2')
create table t2(c21 varchar(10),c22 varchar(10))
insert into t2 values('A' , 'A_D')
insert into t2 values('A1', 'A1_D')
insert into t2 values('A2', 'A2_D')
insert into t2 values('B' , 'B_D')
insert into t2 values('B1', 'B1_D')
insert into t2 values('B2', 'B2_D')
select t3.c11,t3.c22,t4.* from
(
select t1.*,t2.c22 from t1 , t2 where t1.c11 = t2.c21
) t3
left join
(
select t2.c21,t2.c22 from t1 , t2 where t1.c12 = t2.c21
) t4
on t3.c12 = t4.c21drop table t1,t2/*
c11 c22 c21 c22
---------- ---------- ---------- ----------
A A_D A1 A1_D
A A_D A2 A2_D
B B_D B1 B1_D
B B_D B2 B2_D(所影响的行数为 4 行)
*/
insert into t1 values('A', 'A1')
insert into t1 values('A', 'A2')
insert into t1 values('B', 'B1')
insert into t1 values('B', 'B2')
create table t2(c1 varchar(10),c2 varchar(10))
insert into t2 values('A' , 'A_D')
insert into t2 values('A1', 'A1_D')
insert into t2 values('A2', 'A2_D')
insert into t2 values('B' , 'B_D')
insert into t2 values('B1', 'B1_D')
insert into t2 values('B2', 'B2_D')select t1.c1,t2.c2,t1.c2,t2.c2 from t1 left join t2 on t1.c1 = t2.c1 left join t2 t3 on t1.c2 = t3.c1drop table t1,t2/*
c1 c2 c2 c2
---------- ---------- ---------- ----------
A A_D A1 A_D
A A_D A2 A_D
B B_D B1 B_D
B B_D B2 B_D(所影响的行数为 4 行)
*/
A.a,
B.b,
A.b,
C.b,
From
T1 As A
Left Join T2 As B
On A.a=B.a
Left Join T2 As C
On A.b=C.a
---创建测试环境
Create Table T1(a varchar(10),b varchar(10))
Insert T1 Select 'A', 'A1'
Union All Select 'A' ,'A2'
Union All Select 'B' ,'B1'
Union All Select 'B' ,'B2'Create Table T2(a varchar(10),b varchar(10))
Insert T2 Select 'A', 'A_D'
Union All Select 'A1', 'A1_D'
Union All Select 'A2', 'A2_D'
Union All Select 'B', 'B_D'
Union All Select 'B1', 'B1_D'
Union All Select 'B2', 'B2_D'
---查询结果
Select
A.a,
B.b,
A.b,
C.b
From
T1 As A
Left Join T2 As B
On A.a=B.a
Left Join T2 As C
On A.b=C.a
---删除测试环境
Drop Table T1,T2
---结果
/*
a b b b
---------- ---------- ---------- ----------
A A_D A1 A1_D
A A_D A2 A2_D
B B_D B1 B1_D
B B_D B2 B2_D(所影响的行数为 4 行)
*/
insert into t1 values('A', 'A1')
insert into t1 values('A', 'A2')
insert into t1 values('B', 'B1')
insert into t1 values('B', 'B2')
create table t2(c21 varchar(10),c22 varchar(10))
insert into t2 values('A' , 'A_D')
insert into t2 values('A1', 'A1_D')
insert into t2 values('A2', 'A2_D')
insert into t2 values('B' , 'B_D')
insert into t2 values('B1', 'B1_D')
insert into t2 values('B2', 'B2_D')
select a.c11,b.c22,a.c12,c.c22
from t1 a left join t2 b on a.c11=b.c21
left join t2 c on a.c12=c.c21 c11 c22 c12 c22
---------- ---------- ---------- ----------
A A_D A1 A1_D
A A_D A2 A2_D
B B_D B1 B1_D
B B_D B2 B2_D(4 row(s) affected)
inner join t2 b
on a.c1=b.c1
inner join t2 c
on c.c1=a.c2结果:
c1 c2 c2 c2
---------- ---------- ---------- ----------
A A_D A1 A1_D
A A_D A2 A2_D
B B_D B1 B1_D
B B_D B2 B2_D
insert alphaA
select 'A', 'A1'
union select 'A','A2'
union select 'B','B1'
union select 'B','B2'create table alphaB(a varchar(10),b varchar(10))
insert alphaB
select 'A', 'A_D'
union select 'A1', 'A1_D'
union select 'A2', 'A2_D'
union select 'B', 'B_D'
union select 'B1', 'B1_D'
union select 'B2', 'B2_D'select a.a,b.b,a.b,c.b from alphaA a left join alphaB b on a.a=b.a left join alphaB c on a.b=c.a
结果:
a b b b
---------- ---------- ---------- ----------
A A_D A1 A1_D
A A_D A2 A2_D
B B_D B1 B1_D
B B_D B2 B2_D