mysql练习——“查询选修“张三“老师所授课程的学生中，成绩最高的学生信息及其成绩”SQL语句；使用表连接，子查询及limit

1.数据准备

数据表1：student

数据表2：stu_sco

数据表3：course

数据表4：teacher

2. 思路分析

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

思路：查询结果是成绩最高的学生信息和学生成绩，约束条件是学张三老师的课程

从数据表中查看，需要借助多张表实现该需求；

数据表公共字段 :student→(s_id)→stu_sco→(c_id)→course→(t_id)→teacher(t_name)

需要进行多表关联才能获取到老师张三的信息；在获取信息后，还需要取最高的成绩，

目前可以用2种方法取最大值

方法1：对数据进行降序排序，使用limit取第一行的成绩

方法2：使用函数max取成绩的最大值

3.SQL实现

3.1 写法1

使用多表连接，并对成绩进行降序排序，取第一行的成绩

select student.*,course.*,stu_sco.score
from student join stu_sco on student.s_id = stu_sco.s_id
join course on stu_sco.c_id = course.c_id
join teacher on course.t_id = teacher.t_id
and t_name = '张三' order by stu_sco.score desc limit 0,1;

注：此处在on后面之间跟了and，数据表连接并进行了过滤

select student.*,course.*,stu_sco.score
from student join stu_sco on student.s_id = stu_sco.s_id
join course on stu_sco.c_id = course.c_id
join teacher on course.t_id = teacher.t_id
where t_name = '张三' order by stu_sco.score desc limit 0,1;

注：此处在on后面之间使用了where，数据表连接完成后再进行条件过滤；

说明：数据量较少时，两者性能差别不大，数据量较多时，建议使用“on...and”会提升一定的效率；同时，此处使用limit只取了第一行成绩，若是存在并列第一的情况，会少取了其他同学的信息。

3.2 写法2

3.2.1 分析

1）多表连接与子查询结合使用；

2）子查询查询最高成绩和张三老师对应可课程的课程id；

3）直接获取符合条件的成绩。

3.2.2 实现

1)子查询1：查询张三老师课程的最高成绩；

select max(stu_sco.score) from stu_sco 
inner join course on stu_sco.c_id = course.c_id
inner join teacher on course.t_id = teacher.t_id
and teacher.t_name = '张三';

2）子查询2：查询张三老师对应可课程的课程id；

select distinct stu_sco.c_id from stu_sco 
inner join course on stu_sco.c_id = course.c_id
inner join teacher on course.t_id = teacher.t_id
and teacher.t_name = '张三';

3）外面的查询负责查询学生信息和成绩；

select student.*,stu_sco.score
from student inner join stu_sco on stu_sco.s_id = student.s_id;

4）增加约束条件并查询

select student.*,stu_sco.score
from student inner join stu_sco on stu_sco.s_id = student.s_id
where stu_sco.score = 
(select max(stu_sco.score) from stu_sco 
inner join course on stu_sco.c_id = course.c_id
inner join teacher on course.t_id = teacher.t_id
and teacher.t_name = '张三')
and stu_sco.c_id = 
(select distinct stu_sco.c_id from stu_sco 
inner join course on stu_sco.c_id = course.c_id
inner join teacher on course.t_id = teacher.t_id
and teacher.t_name = '张三');

说明：约束条件直接指定了成绩和课程id，在同一个课程中，即使存在并列第一的情况也能够查询出来；但是这个SQL语句较长。