select b.id,b.SmallName,BiGID=a.ID,a.TypeName
from 表一 a
left join 表二 b on a.ID=b.BiGID
and b.ID in(
select top 2 ID from 表二
where BiGID=a.BiGID)
from 表一 a
left join 表二 b on a.ID=b.BiGID
and b.ID in(
select top 2 ID from 表二
where BiGID=a.BiGID)
select b.ID,b.SmallName,BiGID=a.ID,a.TypeName
from 表一 a
left join 表二 b on a.ID=b.BiGID
and b.ID in(
select top 3 ID from 表二
where BiGID=a.ID)
order by case when b.ID is null then 1 else 0 end,b.ID
create table 表一(ID int,TypeName varchar(10))
insert 表一 select 1,'a'
union all select 2,'b'
union all select 3,'c'create table 表二(ID int,SmallName varchar(10),BiGID int,TypeName varchar(10))
insert 表二 select 1,'Smala',2,'b'
union all select 2,'Smalb',2,'b'
union all select 3,'Smalc',2,'b'
union all select 4,'Smald',2,'b'
union all select 5,'Smal4',3,'c'
go--查询
select b.ID,b.SmallName,BiGID=a.ID,a.TypeName
from 表一 a
left join 表二 b on a.ID=b.BiGID
and b.ID in(
select top 3 ID from 表二
where BiGID=a.ID)
order by case when b.ID is null then 1 else 0 end,b.ID
go--删除测试
drop table 表一,表二/*--测试结果ID SmallName BiGID TypeName
----------- ---------- ----------- ----------
1 Smala 2 b
2 Smalb 2 b
3 Smalc 2 b
5 Smal4 3 c
NULL NULL 1 a(所影响的行数为 5 行)
--*/