select
    t.*
from
    表 t
where
    t.stuid in(select top 2 stuid from 表 where courseId=t.courseId order by score desc)
order by
    t.courseId,t.score desc

------解决方案--------------------

SQL code
--參考
我们经常会有这样的需求，即按照地区来分别取出每个地区排名前3的那些记录.

首先，创建测试用的表和数据，如下：

create table test
(
areaid int,
score int
)
insert into test select 0,10
union all select 0,20
union all select 0,30
union all select 0,40
union all select 0,50
union all select 1,10
union all select 1,20
union all select 1,30
union all select 1,40
union all select 1,50
union all select 2,10
union all select 2,20
union all select 2,30
union all select 2,40
union all select 2,50
go

第一种方法适用于sql2000和2005，其代码如下：

select * from test a
where checksum(*) in (select top 3 checksum(*) from test b where a.areaid=b.areaid order by score desc)

第二种方法是利用sql2005的函数ROW_NUMBER，其代码如下：

WITH test1 AS
(
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY areaid ORDER BY score desc) AS 'RowNumber'
    FROM test 
) 
SELECT * 
FROM test1 
WHERE RowNumber BETWEEN 1 AND 3;

第三种方法是利用sql2005的cross apply来实现，其代码如下：

select  distinct t.* from test a
cross apply
(select top 3 areaid,score from test
where a.areaid=areaid order by score desc) as T

------解决方案--------------------

select
   t.*
from
   表 t
where
   t.stuid in(select top 2 stuid from 表 where courseId=t.courseId order by score desc)
order by
   t.courseId,t.score desc