极客笔记MySQL 三表连接
在MySQL的查询语句中，我们常常需要联结多张表进行关联查询。一张表的查询可以使用简单的SELECT语句来解决，但多张表的查询就不那么容易了。因此，我们需要用到MySQL的三表连接。
阅读更多：MySQL 教程
什么是三表连接？
三表连接可以理解为将三张表通过某些共同列进行连接，从而将他们的数据进行关联和整合，并得出我们需要的结果。
MySQL三表连接的语法
MySQL的三表连接语法如下所示：
SELECT * FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column
LEFT JOIN table3 ON table1.common_column = table3.common_column
WHERE condition
以上语法中的table1、table2和table3代表三张表，common_column代表三张表之间的共同列，condition是用来限制查询结果的条件。
这里我们用三个表的例子来说明：
首先，我们准备三张表，分别为students、scores和courses。
students表如下：
| id | name | gender | age | address |
|---|---|---|---|---|
| 1 | Alice | F | 18 | 123 Main Street |
| 2 | Bob | M | 19 | 456 Park Ave |
| 3 | Carol | F | 20 | 789 Madison Ave |
| 4 | Dave | M | 21 | 1354 Broadway |
| 5 | Elvira | F | 22 | 2468 5th Ave |
scores表如下：
| id | student_id | course_id | score |
|---|---|---|---|
| 1 | 1 | 1 | 95 |
| 2 | 1 | 2 | 85 |
| 3 | 1 | 3 | 90 |
| 4 | 2 | 1 | 70 |
| 5 | 2 | 2 | 80 |
| 6 | 2 | 3 | 75 |
| 7 | 3 | 1 | 80 |
| 8 | 3 | 2 | 85 |
| 9 | 3 | 3 | 90 |
| 10 | 4 | 1 | 75 |
| 11 | 4 | 2 | 80 |
| 12 | 4 | 3 | 85 |
| 13 | 5 | 1 | 70 |
| 14 | 5 | 2 | 75 |
| 15 | 5 | 3 | 65 |
courses表如下：
| id | name | teacher |
|---|---|---|
| 1 | Math | Mr. Smith |
| 2 | English | Ms. Jones |
| 3 | Science | Mr. Lee |
| 4 | History | Ms. Chen |
| 5 | Literature | Mr. Wang |
| 6 | Art | Ms. Liu |
我们需要从以上三个表中查询出students表中所有学生的成绩（包括没有参加课程的学生），每门课程的平均分数以及平均分数最高的课程。
因为要查询三张表，我们需要使用三表连接。这个具体过程如下：
SELECT students.name, scores.course_id, scores.score, AVG(scores.score) AS avg_score,
AVG(CASE WHEN courses.id = 1 THEN scores.score ELSE NULL END) AS avg_score_math,
AVG(CASE WHEN courses.id = 2 THEN scores.score ELSE NULL END) AS avg_score_english,
AVG(CASE WHEN courses.id = 3 THEN scores.score ELSE NULL END) AS avg_score_science,
AVG(CASE WHEN courses.id = 4 THEN scores.score ELSE NULL END) AS avg_score_history,AVG(CASE WHEN courses.id = 5 THEN scores.score ELSE NULL END) AS avg_score_literature,
AVG(CASE WHEN courses.id = 6 THEN scores.score ELSE NULL END) AS avg_score_art,
MAX(CASE WHEN scores.course_id = max_score_course.id THEN scores.score ELSE NULL END) AS max_score,
courses.name AS max_score_course
FROM students
LEFT JOIN scores ON students.id = scores.student_id
LEFT JOIN courses ON scores.course_id = courses.id
LEFT JOIN (SELECT course_id, MAX(score) AS max_score FROM scores GROUP BY course_id) AS max_score_course ON scores.course_id = max_score_course.course_id AND scores.score = max_score_course.max_score
GROUP BY students.id
我们首先使用LEFT JOIN将students表连接到scores表，连接条件为students.id = scores.student_id。然后，我们再使用LEFT JOIN将scores表连接到courses表，连接条件为scores.course_id = courses.id。接下来，我们使用LEFT JOIN将scores表连接到一个子查询max_score_course，这个子查询用来找出每门课程的最高分数及其对应的课程ID，即MAX(CASE WHEN scores.course_id = max_score_course.id THEN scores.score ELSE NULL END) AS max_score, courses.name AS max_score_course。
最后，我们在SELECT语句中使用一些聚合函数来计算平均分数，以及获取平均分数最高的课程，即AVG(scores.score) AS avg_score, AVG(CASE WHEN courses.id = 1 THEN scores.score ELSE NULL END) AS avg_score_math, AVG(CASE WHEN courses.id = 2 THEN scores.score ELSE NULL END) AS avg_score_english, AVG(CASE WHEN courses.id = 3 THEN scores.score ELSE NULL END) AS avg_score_science, AVG(CASE WHEN courses.id = 4 THEN scores.score ELSE NULL END) AS avg_score_history, AVG(CASE WHEN courses.id = 5 THEN scores.score ELSE NULL END) AS avg_score_literature, AVG(CASE WHEN courses.id = 6 THEN scores.score ELSE NULL END) AS avg_score_art, MAX(CASE WHEN scores.course_id = max_score_course.id THEN scores.score ELSE NULL END) AS max_score, courses.name AS max_score_course。
最后，我们使用GROUP BY语句来确保每个学生只出现一次，并且在查询结果中包含所有的学生、成绩以及课程相关信息。
总结
MySQL三表连接可以帮助我们用一个查询得到三张关联表的所有信息。通过这篇文章，我们学习了三表连接的语法以及一个示例查询。希望这篇文章对您有所帮助。