如果要查出每个课程的前三名的学生信息，应该怎么写？

Question

有三个表：student course score 下面我给出score的创建脚本 
create table score 
( 
scoreID int identity(1,1) primary key, 
studentID int, 
courseID int, 
score int)//studentID,courseID是外键，引用student,course表，因为跟我做的业务逻辑无关，这里没写，我指出而已 
//插入测试数据 
insert into score values(16,1,66) 
insert into score values(23,1,56) 
insert into score values(25,1,67) 
insert into score values(27,1,45) 
insert into score values(28,1,70) 
insert into score values(29,1,71) 
insert into score values(30,1,60) insert into score values(16,2,96) 
insert into score values(23,2,86) 
insert into score values(25,2,67) 
insert into score values(27,2,55) 
insert into score values(28,2,80) 
insert into score values(29,2,51) 
insert into score values(30,2,60) insert into score values(16,3,86) 
insert into score values(23,3,85) 
insert into score values(25,3,69) 
insert into score values(27,3,56) 
insert into score values(28,3,72) 
insert into score values(29,3,75) 
insert into score values(30,3,69) 
就是如果要查出每个课程的前三名的学生信息，应该怎么写？

Answer 1

2000使用临时表+课程列,
2005使用ROW_NUMBER + PARTITION BY 课程列

Answer 2

create table score 
( 
scoreID int identity(1,1) primary key, 
studentID int, 
courseID int, 
score int)
insert into score values(16,1,66) 
insert into score values(23,1,56) 
insert into score values(25,1,67) 
insert into score values(27,1,45) 
insert into score values(28,1,70) 
insert into score values(29,1,71) 
insert into score values(30,1,60) insert into score values(16,2,96) 
insert into score values(23,2,86) 
insert into score values(25,2,67) 
insert into score values(27,2,55) 
insert into score values(28,2,80) 
insert into score values(29,2,51) 
insert into score values(30,2,60) insert into score values(16,3,86) 
insert into score values(23,3,85) 
insert into score values(25,3,69) 
insert into score values(27,3,56) 
insert into score values(28,3,72) 
insert into score values(29,3,75) 
insert into score values(30,3,69) 
WITH scores AS
(
select  * ,ROW_NUMBER() OVER (PARTITION BY courseID order BY  score desc )  AS 'RowNumber'
  from score
  ) 
SELECT * 
FROM scores
  where RowNumber<46 29 1 71 1
5 28 1 70 2
3 25 1 67 3
8 16 2 96 1
9 23 2 86 2
12 28 2 80 3
15 16 3 86 1
16 23 3 85 2
20 29 3 75 3

Answer 3

select * from score a
WHERE StudentID IN (
SELECT TOP 3 StudentID
FROM score 
WHERE courseID=a.courseID
ORDER BY score DESC
)