求一sql语句。详情如下：

Question

create table student
(StudentID int, CourseID int, Archive float)insert student
select 1,1,91
union all select 2,1,92
union all select 3,1,93
union all select 4,2,94
union all select 5,2,95
union all select 6,2,96
union all select 7,3,97
union all select 8,3,98
union all select 9,3,99select studentID, courseID, archive from
(select studentID, CourseID, Archive, row_number() over(partition by courseID order by current_timestamp) as pid
from student)s
where pid = 1 or pid = 2studentID   courseID    archive
----------- ----------- ----------------------
1           1           91
2           1           92
4           2           94
5           2           95
7           3           97
8           3           98

Answer 1

declare @T table(StudentId int,CourseId int,Archive int)
insert @T select 1, 1, 91 
union all select 2, 1, 92 
union all select 3, 1, 93 
union all select 4, 2, 94 
union all select 5, 2, 95 
union all select 6, 2, 96 
union all select 7, 3, 97 
union all select 8, 3, 98 
union all select 9, 3, 98 select * from @T t where StudentId in(select top 2 StudentId from @T where CourseId=t.CourseId)
（所影响的行数为 9 行）StudentId   CourseId    Archive     
----------- ----------- ----------- 
1           1           91
2           1           92
4           2           94
5           2           95
7           3           97
8           3           98（所影响的行数为 6 行）

Answer 2

declare @T table(StudentId int,CourseId int,Archive int)
insert @T select 1, 1, 91 
union all select 2, 1, 92 
union all select 3, 1, 93 
union all select 4, 2, 94 
union all select 5, 2, 95 
union all select 6, 2, 96 
union all select 7, 3, 97 
union all select 8, 3, 98 
union all select 9, 3, 98 select * from @T t where StudentId in(select top 2 StudentId from @T where CourseId=t.CourseId order by Archive asc )--加排序取成绩最小的两名
（所影响的行数为 9 行）StudentId   CourseId    Archive     
----------- ----------- ----------- 
1           1           91
2           1           92
4           2           94
5           2           95
7           3           97
9           3           98（所影响的行数为 6 行）

Answer 3

declare @T table(StudentId int,CourseId int,Archive int)
insert @T select 1, 1, 91 
union all select 2, 1, 92 
union all select 3, 1, 93 
union all select 4, 2, 94 
union all select 5, 2, 95 
union all select 6, 2, 96 
union all select 7, 3, 97 
union all select 8, 3, 98 
union all select 9, 3, 98 select * from @t a where exists(select * from @t where courseid=a.courseid and studentid>a.studentid)

Answer 4

if object_id('student') is not null
drop table studentcreate table student
(StudentID int, CourseID int, Archive float)insert student
select 1,1,91
union all select 2,1,92
union all select 3,1,93
union all select 4,2,94
union all select 5,2,95
union all select 6,2,96
union all select 7,3,97
union all select 8,3,98
union all select 9,3,99select * from student
/*
StudentID   CourseID    Archive                                               
----------- ----------- ----------------------------------------------------- 
1           1           91.0
2           1           92.0
3           1           93.0
4           2           94.0
5           2           95.0
6           2           96.0
7           3           97.0
8           3           98.0
9           3           99.0
*/select * from student a where StudentID in(select top 2 StudentID from student where a.CourseID=CourseID order by 1)
/*
StudentID   CourseID    Archive                                               
----------- ----------- ----------------------------------------------------- 
1           1           91.0
2           1           92.0
4           2           94.0
5           2           95.0
7           3           97.0
8           3           98.0
*/

Answer 5

if object_id('student') is not null
drop table studentcreate table student
(StudentID int, CourseID int, Archive float)insert student
select 1,1,91
union all select 2,1,92
union all select 3,1,93
union all select 4,2,94
union all select 5,2,95
union all select 6,2,96
union all select 7,3,97
union all select 8,3,98
union all select 9,3,99select * from student
/*
StudentID   CourseID    Archive                                               
----------- ----------- ----------------------------------------------------- 
1           1           91.0
2           1           92.0
3           1           93.0
4           2           94.0
5           2           95.0
6           2           96.0
7           3           97.0
8           3           98.0
9           3           99.0
*/--方法一:
select * from student a where StudentID in(select top 2 StudentID from student where a.CourseID=CourseID order by 1)
/*
StudentID   CourseID    Archive                                               
----------- ----------- ----------------------------------------------------- 
1           1           91.0
2           1           92.0
4           2           94.0
5           2           95.0
7           3           97.0
8           3           98.0
*/
--方法二:
select * from student a where (select count(1) from student where a.CourseID=CourseID and a.StudentID>StudentID)<2
/*
StudentID   CourseID    Archive                                               
----------- ----------- ----------------------------------------------------- 
1           1           91.0
2           1           92.0
4           2           94.0
5           2           95.0
7           3           97.0
8           3           98.0
*/

Answer 6

--oracle下使用分析函数比较简单
select studentID, courseID, archive from
(select studentID, CourseID, Archive, row_number() over(partition by courseID order by archive  DESC) as pid
from student)s
--mssql
select * from student a 
where StudentID in(
select top 2 StudentID from student where a.CourseID=CourseID order by Archive desc
) order by a.Archive desc

Answer 7

字段CourseId上建立索引效果会更好^o^

Answer 8

declare @T table(StudentId int,CourseId int,Archive int)
insert @T select 1, 1, 91 
union all select 2, 1, 92 
union all select 3, 1, 93 
union all select 4, 2, 94 
union all select 5, 2, 95 
union all select 6, 2, 96 
union all select 7, 3, 97 
union all select 8, 3, 98 
union all select 9, 3, 98 
select * from @T a
where (select count(1) from @T where CourseId=a.CourseId and StudentId<a.StudentId)<2
（所影响的行数为 9 行）StudentId   CourseId    Archive     
----------- ----------- ----------- 
1           1           91
2           1           92
4           2           94
5           2           95
7           3           97
8           3           98（所影响的行数为 6 行）

Answer 9

in ( top 2 ...)2>=(select count ....