如果得出这个结果?

[rongxh]

原始数据:
Table1
Col1 col2 col3
A A1 A2
B B1 B2
C C1 C2
Table2
Col1 Col2
A1 120
A2 200
B1 100
C2 300
结果数据:
Col1 col2 col3
A 120 200
B 100
C 300

[lsxaa]

select Col1
,col2=isnull(select top 1 Col2 from table2 where col1=table1.col1,'')
,col3=isnull(select top 1 Col2 from table2 where col1=table1.col2,'')
from table1

[Andy__Huang]

select a.col1,b.col2,c.col2 from tb1 a
left join tb2 b on a.col2=b.col1
left join tb2 c on a.col3=b.col1

[Andy__Huang]

create table tb1(Col1 varchar(10),col2 varchar(10), col3 varchar(10))
Insert into tb1
select 'A','A1','A2'
union all select 'B','B1','B2'
union all select 'C','C1','C2'

create table tb2(Col1 varchar(10),col2 numeric(10))
Insert into tb2
select 'A1','120'
union all select 'A2','200'
union all select 'B1','100'
union all select 'C2','300'

select * from tb1
select * from tb2

drop table tb1,tb2

select a.col1,b.col2,col3=c.col2 from tb1 a
left join tb2 b on a.col2=b.col1
left join tb2 c on a.col3=c.col1

--結果
col1 col2 col3
---------------------------
A 120 200
B 100 NULL
C NULL 300

[chinaandys]

select a.col1,b.col2,col3=c.col2 from table1 a
left join table2 b on a.col2=b.col1
left join table2 c on a.col3=c.col1
---------------------------
A 120 A2 200
B 100 NULL NULL
C NULL C2 300

[MorningTea]

前面会有null,我写了一个没有null,但是有没有null抓取到数据集给系统用的时候应该没有什么影响吧?
/*资料存在
T1:
A A1 A2
B B1 B2
C C1 C2
T2:
A1 120
A2 200
B1 100
C2 300
*/
if object_id('tempdb.dbo.#t3') is not null
drop table #t3 --如果有存在临时table为#t3的则删除
if object_id('tempdb.dbo.#t4') is not null
drop table #t4 --如果有存在临时table为#t4的则删除
select T1.col1, col2 = case isnull(t2.col2,'0') --如果为空则替换为0
when 0 then '' --如果为0则替换为返回''
else convert(varchar,t2.col2)
end
into #t4
from t1 left join t2 on t1.col2 = t2.col1

select T1.col1, col3 = case isnull(t2.col2,'0') --如果为空则替换为0
when 0 then '' --如果为0则替换为返回''
else convert(varchar,t2.col2)
end
into #t3
from t1 left join t2 on t1.col3 = t2.col1

select #t3.col1,#t4.col2,#t3.col3 from
#t3 inner join #t4
on #t3.col1 = #t4.col1

[lsxaa]

晕 这都那年的帖子了