select TA.A , TB.B ,
(select TableC.A from TableC where B = left(TA.B,2)) as C1,
(select TableC.A from TableC where B = right(TA.B,2)) as C2
from TableA TA,TableB TB
where TA.C= TB.C
(select TableC.A from TableC where B = left(TA.B,2)) as C1,
(select TableC.A from TableC where B = right(TA.B,2)) as C2
from TableA TA,TableB TB
where TA.C= TB.C
from tableA,tableB,tableC
where TableA.C=TableB.C
and right(rtrim(TableA.B),2)=rtrim(TableA.B)
and tableA.A='a'
tablea left join tableb on tablea.c=tableb.c
left join tablec on left(tablea.b,2)=tablec.b or right(tablea.b,2)=tablec.b结果为:TableA.A TableB.B TableC.A
a 123 abc
a 123 def
create table #TableA(A varchar(10),B varchar(20),C varchar(20))
insert into #TableA select 'a', '2233', '111'
insert into #TableA select ' b', '3344', '222'
insert into #TableA select ' c', '4455', '333'create table #TableB(A varchar(10),B varchar(20),C varchar(20))
insert into #TableB select ' 1', '123', '111'
insert into #TableB select ' 2', '345', '222'
insert into #TableB select ' 3', '456', '333'create table #TableC(A varchar(10),B varchar(10))
insert into #TableC select ' abc', '22'
insert into #TableC select 'def', '33'
insert into #TableC select 'ghi', '44'
select TA.A , TB.B ,
(select #TableC.A from #TableC where B = left(TA.B,2)) as C1,
(select #TableC.A from #TableC where B = right(TA.B,2)) as C2
from #TableA TA,#TableB TB
where TA.C= TB.C ----- and TA.A='a'drop table #tableA,#tableB,#tablec