求一sql语句。详情如下：
有一表Student，有3个字段：StudentId（学生编号，int型）、CourseId（课程编号，int型）、Archive（成绩，float型），内容如下：
StudentIdCourseIdArchive
1191
2192
3193
4294
5295
6296
7397
8398
9398
－－－－－－－－－－－－
求每门课程的前两个学生的成绩。结果如下：
StudentIdCourseIdArchive
1191
2192
4294
5295
7397
8398
这样的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
----------- ----------- -----------------------------------------------------
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
*/
[解决办法]
我觉得你们都需要考虑一下：
求每门课程的前两个学生的成绩的意思应该是
求每门课程成绩排名前2名的学生，这其中还有一个需要注意的是：成绩有可能一样，
[解决办法]
--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
[解决办法]
in ( top 2 ...)
2>=(select count ....