38、成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
解法1:
SELECT S.sid,S.sname,SC.score AS 最高成绩
FROM student AS S INNER JOIN SC
ON S.sid=SC.sid
WHERE SC.score=
( SELECT MAX(score)
FROM SC INNER JOIN course AS C
ON SC.cid=C.cid
INNER JOIN teacher AS T
ON C.tid=T.tid
where Tname='张三'
);
sid | sname | 最高成绩
------------+------------------------+----------
01 | 赵雷 | 90
(1 行记录)
解法2:
SELECT *
FROM(
SELECT S.sid, S.sname, SC.score,
rank(