--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
go 
create table [test]([id] int,[name] varchar(4),[gradename] varchar(6),[gradeid] int,[brownum] int,[age] int)
insert [test]
select 1,'张三','一年级',1,11,7 union all
select 8,'张四','二年级',2,16,8 union all
select 2,'张五','二年级',2,17,7 union all
select 4,'张六','四年级',4,16,9 union all
select 6,'张七','三年级',3,18,7 union all
select 5,'张八','二年级',2,19,9 union all
select 3,'张九','四年级',4,21,10 union all
select 7,'张十','一年级',1,41,7 union all
select 9,'李一','一年级',1,71,7 union all
select 10,'李二','三年级',3,99,7 union all
select 11,'李四','四年级',4,88,10
go

select * from test a
where [brownum]=(select top 2 [brownum] from test b where a.[name]=b.[name] order by [brownum] desc)

/*
1    张三    一年级    1    11    7
8    张四    二年级    2    16    8
2    张五    二年级    2    17    7
4    张六    四年级    4    16    9
6    张七    三年级    3    18    7
5    张八    二年级    2    19    9
3    张九    四年级    4    21    10
7    张十    一年级    1    41    7
9    李一    一年级    1    71    7
10    李二    三年级    3    99    7
11    李四    四年级    4    88    10
*/

------解决方案--------------------
SQL code
select 
  distinct b.*
from
  user a
cross apply
  (select top 2 * from user where gradeid=t.gradeid order by brownum desc)b

------解决方案--------------------
SQL code

select id,name,gradename,gradeid,brownum,age 
from
(
    select row_number() over(partition by gradeid order by brownum desc) rn,* 
    from
    (
        select id name gradename gradeid brownum age from user
    )t
)tt
where tt.rn<=2

------解决方案--------------------
SQL code
select  * from test a
where [brownum]IN (select top 2 [brownum] from test b where a.[gradename]=b.[gradename] order by [brownum] desc)
ORDER BY [gradename]  ,[brownum] DESC