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,99

select 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 = 2

------解决方案--------------------
SQL code
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 行） 
  
 


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

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)

------解决方案--------------------
if object_id('student') is not null
drop table student

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,99

select * 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                                              
----------- ----------- ------------------------------------------------