成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
为了验证这一题,先修改原始数据
UPDATE sc SET score=90
where sid = "07"
and cid ="02";
这样张三老师教的02号课就有两个学生同时获得90的最高分了。
这道题的思路继续上一题,我们已经查询到了符合限定条件的最高分了,这个时候只用比较这张表,找到全部score等于这个最高分的记录就可,看起来有点繁复。
select student.*, sc.score, sc.cid from student, teacher, course,sc
where teacher.tid = course.tid
and sc.sid = student.sid
and sc.cid = course.cid
and teacher.tname = "张三"
and sc.score = (
select Max(sc.score)
from sc,student, teacher, course
where teacher.tid = course.tid
and sc.sid = student.sid
and sc.cid = course.cid
and teacher.tname = "张三"
);