select b.id,b.desc,a.f1 from a join b on a.id=b.id and a.no=b.desc
--不要用desc做字段名,最好不要用关键字做字段名 create table #lfy(id int,no int,f1 varchar(20)) go insert into #lfy select 1,9022,'a1' union all select 2,9022,'a1' union all select 3,9022,'a1' union select 4,9022,'a1'create table #lfy1(id int,desc1 int) go insert into #lfy1 select 1,9022 union all select 2,9022 union all select 3,9022select b1.id,b1.desc1,(select distinct f1 from #lfy where no=b1.desc1) as f1 from #lfy1 as b1drop table #lfy drop table #lfy1
from a join b on a.id=b.id and a.no=b.desc
create table #lfy(id int,no int,f1 varchar(20))
go
insert into #lfy
select 1,9022,'a1'
union all
select 2,9022,'a1'
union all
select 3,9022,'a1'
union
select 4,9022,'a1'create table #lfy1(id int,desc1 int)
go
insert into #lfy1
select 1,9022
union all
select 2,9022
union all
select 3,9022select b1.id,b1.desc1,(select distinct f1 from #lfy where no=b1.desc1) as f1 from #lfy1 as b1drop table #lfy
drop table #lfy1