一张sql的成绩表,查询各科成绩前两名(考虑并列)

hwangita 2016-04-20 04:17:07
如题,最近学习sql(mysql环境,没有top这种扩展函数,mysql 板块没多少人,特来求救),

遇到一个难题,学生成绩表scores,id,stuid,courid,score


要求查询各科(courid)成绩前两名的stuid,

我自己写了个左连接查询,当成绩没有并列的时候还好,一旦有并列,就不对了,求指教。最好一步完成,不用top函数
select *
from study.scores s1 left join study.scores s2
on s1.courid=s2.courid and s1.score<s2.score
group by s1.courid,s1.score
having count(s1.id)<2
order by s1.courid,s1.score desc
...全文
779 3 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
zhaoanle 2016-04-21
  • 打赏
  • 举报
回复
Create table scores (id int,stuid varchar(10),courid varchar(10),score int) insert scores select 1,201601,11,99 insert scores select 2,201601,12,99 insert scores select 3,201601,13,99 insert scores select 4,201601,14,99 insert scores select 5,201602,11,99 insert scores select 6,201602,12,100 insert scores select 7,201602,13,88 insert scores select 8,201602,14,66 insert scores select 9,201603,11,33 insert scores select 10,201603,12,88 insert scores select 11,201603,13,55 insert scores select 12,201603,14,44 insert scores select 13,201604,11,99 select (select COUNT(1) from scores where courid = a.courid and score >=a.score ) as sn,* into #t from scores a order by courid select * from #t order by courid ,score desc select * from #t a where sn in (select top 2 sn from #t where courid = a.courid order by sn) drop table #t,scores
spiritofdragon 2016-04-20
  • 打赏
  • 举报
回复
引用 1 楼 spiritofdragon 的回复:
你自己会写mysql的加序号么?如果会,现按score升序,加个序号叫rownum 吧。 然后,再用这个有序号的查询作为你的子查询。用你的写法,但s1.score<s2.score 这改成 s1.rownum=s2.rownum 其他不变。 mysql没有“公共表达式”with的写法,所以,我就不献丑了。
应该是s1.rownum<s2.rownum...
spiritofdragon 2016-04-20
  • 打赏
  • 举报
回复
你自己会写mysql的加序号么?如果会,现按score升序,加个序号叫rownum 吧。 然后,再用这个有序号的查询作为你的子查询。用你的写法,但s1.score<s2.score 这改成 s1.rownum=s2.rownum 其他不变。 mysql没有“公共表达式”with的写法,所以,我就不献丑了。
Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cname,T#) 课程表 SC(S#,C#,score) 成绩 Teacher(T#,Tname) 教师表 问题: 1、查询“001”课程比“002”课程成绩高的所有学生的学号; select a.S# from (select s#,score from SC where C#='001') a,(select s#,score from SC where C#='002') b where a.score>b.score and a.s#=b.s#; 2、查询平均成绩大于60分的同学的学号和平均成绩; select S#,avg(score) from sc group by S# having avg(score) >60; 3、查询所有同学的学号、姓名、选课数、总成绩; select Student.S#,Student.Sname,count(SC.C#),sum(score) from Student left Outer join SC on Student.S#=SC.S# group by Student.S#,Sname 4、查询姓“李”的老师的个数; select count(distinct(Tname)) from Teacher where Tname like '李%'; 5、查询没学过“叶平”老师课的同学的学号、姓名; select Student.S#,Student.Sname from Student where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平'); 6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名; select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002'); 7、查询学过“叶平”老师所教的所有课的同学的学号、姓名; select S#,Sname from Student where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平' group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname='叶平')); 8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名; Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2 from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2 60); 10、查询没有学全所有课的同学的学号、姓名; select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course); 11、查询

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧