有一表Student，有3个字段：StudentId（学生编号，int型）、CourseId（课程编号，int型）、Archive（成绩，float型），内容如下：
StudentId CourseId Archive
1 1 91
2 1 92
3 1 93
4 2 94
5 2 95
6 2 96
7 3 97
8 3 98
9 3 98
－－－－－－－－－－－－
求每门课程的前两个学生的成绩。结果如下：
StudentId CourseId Archive
1 1 91
2 1 92
4 2 94
5 2 95
7 3 97
8 3 98
这样的sql语句该怎样写呢？
------解决方案--------------------
- SQL code
create table student(StudentID int, CourseID int, Archive float)insert studentselect 1,1,91union all select 2,1,92union all select 3,1,93union all select 4,2,94union all select 5,2,95union all select 6,2,96union all select 7,3,97union all select 8,3,98union all select 9,3,99select studentID, courseID, archive from(select studentID, CourseID, Archive, row_number() over(partition by courseID order by current_timestamp) as pidfrom student)swhere 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
----------- ----------- -----------------------------------------------------