select * from tbl as a where 编号 in (select top 2 编号 from tbl where 组名=a.组名 order by 分值 desc)
declare @table table (组名 int,人的编号 int,分值 int)insert into @table select 1,1,90 union select 1,2,91 union select 1,3,91 union select 1,4,92 union select 2,1,80 union select 2,2,85 union select 2,3,91select 组名,人的编号,分值 from @table a where 2>(select count(*) from @table where 组名=a.组名 and 分值>a.分值)
where 编号 in (select top 2 编号 from tbl
where 组名=a.组名 order by 分值 desc)
(组名 int,人的编号 int,分值 int)insert into @table
select 1,1,90
union
select 1,2,91
union
select 1,3,91
union
select 1,4,92
union
select 2,1,80
union
select 2,2,85
union
select 2,3,91select 组名,人的编号,分值
from @table a
where 2>(select count(*) from @table where 组名=a.组名 and 分值>a.分值)