SQL多表联合查询实战指南：从基础连接到复杂统计分析

为什么需要多表查询？在规范的关系型数据库设计中，为了避免数据冗余和空间浪费，信息会被拆分到不同的表中存储。当我们需要从多张表中整合数据时，就必须通过表之间的主外键关联进行联合查询。

首先，查看数据库表结构信息，这是进行连表查询的基础。

mysql> use school;
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| course           |
| sc               |
| student          |
| teacher          |
+------------------+
7 rows in set (0.00 sec)

mysql> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| sno   | int              | NO   | PRI | NULL    | auto_increment |
| sname | varchar(20)      | NO   |     | NULL    |                |
| sage  | tinyint unsigned | NO   |     | NULL    |                |
| ssex  | enum('f','m')    | NO   |     | m       |                |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> desc course;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| cno   | int         | NO   | PRI | NULL    |       |
| cname | varchar(20) | NO   |     | NULL    |       |
| tno   | int         | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc sc;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| sno   | int  | NO   |     | NULL    |       |
| cno   | int  | NO   |     | NULL    |       |
| score | int  | NO   |     | 0       |       |
+-------+------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc teacher;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| tno   | int         | NO   | PRI | NULL    |       |
| tname | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

1）查询平均成绩大于60分的同学的学号和平均成绩

需求分析：
需要关联 student 表和 sc 表，获取每位学生的平均成绩并进行筛选。

表关联与数据预览：

select * from student
join sc on student.sno=sc.sno

（此处省略长结果集）

查询实现：
首先按学号分组计算平均成绩，然后使用 HAVING 子句进行过滤。

select student.sno as "学号", avg(sc.score) as "成绩平均分"
from student
join sc on student.sno=sc.sno
group by student.sno
having avg(sc.score) > 60;

2）查询所有同学的学号、姓名、选课数和总成绩

需求分析：
需要关联 student、sc 和 course 三张表，统计每位学生的选课数量及总分。

表关联与数据预览：

select * from student
join sc on student.sno=sc.sno
join course on sc.cno=course.cno

（此处省略长结果集）

查询实现：
按学生分组，使用 COUNT 和 SUM 聚合函数。

select student.sno, student.sname, count(course.cno), sum(sc.score)
from student
join sc on student.sno=sc.sno
join course on sc.cno=course.cno
group by student.sno;

3）查询各科成绩最高和最低的分，以如下形式显示：课程ID、最高分、最低分

需求分析：
关联 course 表和 sc 表，按课程分组查找极值。

表关联与数据预览：

select * from course
join sc on course.cno=sc.cno;

（此处省略长结果集）

查询实现：
使用 MAX 和 MIN 聚合函数。

select course.cno, max(sc.score), min(sc.score)
from course
join sc on course.cno=sc.cno
group by course.cno;

4）统计各位老师，所教课程的及格率

需求分析：
这是一个典型的数据分析场景，需要关联 teacher、course 和 sc 三张表，并按老师和课程计算及格人数占比。

表关联与数据预览：

select * from teacher
join course on teacher.tno=course.tno
join sc on course.cno=sc.cno

（此处省略长结果集）

查询实现：
结合 CASE WHEN 条件判断和聚合函数进行计算。

select teacher.tname, course.cname,
concat(floor(count(case when sc.score>=60 then 1 end)/count(*)*100),"%")
from teacher
join course on teacher.tno=course.tno
join sc on course.cno=sc.cno
group by teacher.tno, course.cno;

5）查询每门课程被选修的学生数

需求分析：
关联 student、sc 和 course 表，按课程统计学生数量。

表关联与数据预览：
（同上例，略）

查询实现：

select course.cname, count(*)
from student
join sc on student.sno=sc.sno
join course on sc.cno=course.cno
group by course.cno;

6）查询出只选修了一门课程的全部学生的学号和姓名

需求分析：
在关联三张表后，通过分组和 HAVING 子句筛选出选课数量为1的学生。

查询实现：

select student.sno, student.sname
from student
join sc on student.sno=sc.sno
join course on sc.cno=course.cno
group by student.sno
having count(course.cno) = 1;

7）查询选修课程门数超过1门的学生信息

需求分析：
与上一题逻辑类似，改变 HAVING 中的条件即可。

查询实现：

select student.sno, student.sname
from student
join sc on student.sno=sc.sno
join course on sc.cno=course.cno
group by student.sno
having count(course.cno) > 1;

8）统计每门课程：优秀（85分以上）良好（70-85）一般（60-70）不及格（小于60）的学生列表

需求分析：
这是一个多条件分类统计问题，需要结合 CASE WHEN 和 GROUP_CONCAT 函数，将不同分数段的学生姓名合并展示。

查询实现：

select course.cname as 课程名,
group_concat(case when sc.score>85 then student.sname end) as 优秀,
group_concat(case when sc.score>=70 and sc.score<85 then student.sname end) as 良好,
group_concat(case when sc.score>=60 and sc.score<70 then student.sname end) as 一般,
group_concat(case when sc.score<60 then student.sname end) as 不及格
from student
join sc on student.sno=sc.sno
join course on sc.cno=course.cno
group by course.cno;

9）查询平均成绩大于85的所有学生的学号、姓名和平均成绩

需求分析：
关联表后分组计算平均成绩，并设置较高的筛选阈值。

查询实现：

select student.sno, student.sname, avg(sc.score)
from student
join sc on student.sno=sc.sno
join course on sc.cno=course.cno
group by student.sno
having avg(sc.score) > 85;

通过以上九个由浅入深的案例，我们系统性地演练了 JOIN、GROUP BY、HAVING 以及 CASE WHEN 等关键SQL子句在多表查询中的综合运用。掌握这些技巧，对于处理后端开发中复杂的业务数据查询至关重要。