查询选修“张三“老师所授课程的学生中，成绩最高的学生信息及其成绩

#每天进步一点点#

MySQL

查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

1.以名字查出t_id
SELECT t_id FROM teacher WHERE t_name=“张三”

2.以t_id查出对应的课程的c_id
SELECT c_id FROM course WHERE t_id=(SELECT t_id FROM teacher WHERE t_name=“张三”)

3.查出张三老师的学生的最高分
SELECT MAX(s_score) FROM score WHERE c_id=(SELECT c_id FROM course WHERE t_id=(SELECT t_id FROM teacher WHERE t_name=“张三”))

4，结合外连接多表查询
SELECT stu.s_name ,s.s_score,c.c_name FROM student stu
JOIN score s ON stu.s_id =s.s_id
JOIN course c ON s.c_id=c.c_id
JOIN teacher t ON t.t_id=c.t_id
WHERE t.t_name=“张三” AND s.s_score=(SELECT MAX(s_score) FROM score WHERE c_id=(SELECT c_id FROM course WHERE t_id=(SELECT t_id FROM teacher WHERE t_name=“张三”)) )