在SQL Server 数据库中,有一个表StudentAchievement(学生成绩),它有三个字段:StudentID(varchar(8),学生编号),CourseID(varchar(10),课程编号),Achievement(int ,成绩),写一条SQL语句,筛选出每门课程的前两名的学生编号,课程编号,成绩并排序。
...全文
1816打赏收藏
求助一条SQL语句~~谢谢~
在SQL Server 数据库中,有一个表StudentAchievement(学生成绩),它有三个字段:StudentID(varchar(8),学生编号),CourseID(varchar(10),课程编号),Achievement(int ,成绩),写一条SQL语句,筛选出每门课程的前两名的学生编号,课程编号,成绩并排序。
--测试数据
create table stu(sid varchar(8), cid varchar(10), score int)
insert stu select '001', '001', 90
union all select '002', '001', 89
union all select '003', '001', 89
union all select '004', '001', 80
union all select '001', '002', 92
union all select '002', '002', 93
union all select '003', '002', 88
--查询(考虑并列的情况)
select sid, cid, score
from (
select *
,id=(select count(*)+1 from stu
where cid=tt.cid and score>tt.score)
from stu as tt)t
where t.id<=2
order by cid, score desc
--清除
drop table stu
连并列一起输出:
create table stu(sid varchar(8), cid varchar(10), score int)
insert stu select '001', '001', 90
union all select '002', '001', 89
union all select '003', '001', 89
union all select '004', '001', 80
union all select '001', '002', 92
union all select '002', '002', 93
union all select '003', '002', 88
select * from stu order by cid,score
select A.* from stu A where not exists(select 1 from stu where cid=A.cid and score>A.score having count(*)>1)