select tid=indentiy(int,1,1),* into #tmp from b select a.id,b.name from a left join (select c.name,c.id from #tmp c join (select min(tid) as tid ,id from #tmp group by id) d on c.tid=d.tid)) e on a.id=e.id
select id, (select top 1 id from b where id=tem.id) from a tem
select id,(select top 1 name from b where id=aa.id) from a aa
--加上字段名:select id,name=(select top 1 name from b where id=aa.id) from a aa
--测试--测试数据 create table a(id int) insert a select 1create table b(id int,name varchar(10)) insert b select 1,'name1' union all select 1,'name2' union all select 1,'name3' go--查询 select id,name=(select top 1 name from b where id=aa.id) from a aa go--删除测试 drop table a,b/*--测试结果 id name ----------- ---------- 1 name1(所影响的行数为 1 行) --*/
比如:a.id
---------------
1
---------------b.id b.name
-------------------
1 name1
1 name2
1 name3
-------------------一般连接就会出现
a.id b.name
-------------------
1 name1
1 name2
1 name3
-------------------我希望出现
a.id b.name
-------------------
1 name1 (top 1出来的)
-------------------
select a.id,b.name from a left join (select c.name,c.id from #tmp c join (select min(tid) as tid ,id from #tmp group by id) d on c.tid=d.tid)) e on a.id=e.id
(select top 1 id from b where id=tem.id)
from a tem
from a aa
from a aa
create table a(id int)
insert a select 1create table b(id int,name varchar(10))
insert b select 1,'name1'
union all select 1,'name2'
union all select 1,'name3'
go--查询
select id,name=(select top 1 name from b where id=aa.id)
from a aa
go--删除测试
drop table a,b/*--测试结果
id name
----------- ----------
1 name1(所影响的行数为 1 行)
--*/