declare @A1 table(id int,a int)
insert into @A1
select 1,22
union all select 2,33
union all select 3,44
union all select 4,55
declare @B1 table(id int,b int)
insert into @b1
select 1,3
union all select 1,4
union all select 2,5
union all select 2,6
union all select 2,7
union all select 2,8
union all select 3,9
union all select 3,10
union all select 3,11
union all select 1,12
union all select 2,13
select myid=identity(int,1,1),* into #tb from @b1
select * from @a1 a inner join #tb b on a.id=b.id
and b.myid=(select min(myid) from #tb where id=a.id)
drop table #tb
/*--测试结果
id a myid id b
----------- ----------- ----------- ----------- -----------
1 22 1 1 3
2 33 3 2 5
3 44 7 3 9
declare @A1 table(id int)
insert into @A1
select 1
union all select 2
union all select 3
union all select 4
declare @B1 table(id int)
insert into @b1
select 1
union all select 1
union all select 2
union all select 2
union all select 2
union all select 2
union all select 3
union all select 3
union all select 3
union all select 1
union all select 2
select *,(select top 1 * from @b1 where a.id=id) from @a1 a
有错误:
当没有用 EXISTS 引入子查询时,在选择列表中只能指定一个表达式。
---------------------------------------------------------------------
select a.* ,(select top 1 表b的第一列 from b where a.id = b.id), ....,(select top 1 表b的最后一列 from b where a.id = b.id) from a