select * from 表 tem where [order] in (select top 3 [order] from 表 where name=tem.name order by [order] desc)
select * from 表a a where [order] in(select top 3 [order] from 表a whre name=a.name order by [order] desc)
感谢大力!!!如果出现这种情况呢name Order a 7 a 6 a 1 b 5 b 5 b 3 b 3 b 3最后应该是 name Order a 7 a 6 a 1 b 5 b 5 b 3
那就要加自增列了 Select identity(int,1,1) as iid,* into #tmp from 表 select * from #tmp a where iid in(select top 3 iid from #tmp where name=a.name order by iid )
select * from 表 tem where (select count(*) from 表 where name=tem.name and [order]>=tem.[order])<=3
a 7
a 6
a 1
b 5
b 5
b 3
b 3
b 3最后应该是
name Order
a 7
a 6
a 1
b 5
b 5
b 3
Select identity(int,1,1) as iid,* into #tmp from 表
select * from #tmp a where iid in(select top 3 iid from #tmp where name=a.name order by iid )