成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
用having max()理论上也是对的,但是下面那种按分数排序然后取limit 1的更直观可靠
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 = "张三"
having max(sc.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 = "张三"
order by score desc
limit 1;