MySQL高效更新：使用LEFT JOIN将学生分数更新为关联表中的最大值
本文探讨如何利用MySQL的LEFT JOIN高效地更新学生表中的分数，使其与关联表中的最大分数保持一致。
场景描述：
假设我们有两个表：student表存储学生信息，score表存储学生成绩。我们需要将student表中每个学生的score字段更新为score表中该学生对应student_id的最大分数。
表结构：
-
student表：-
id(INT, 主键) -
name(VARCHAR) -
score(INT)
-
-
score表：-
id(INT, 主键) -
student_id(INT, 外键关联student表的id) -
score(INT)
-
解决方案：
直接使用UPDATE语句结合子查询即可实现：
UPDATE student s
LEFT JOIN (
SELECT student_id, MAX(score) as max_score
FROM score
GROUP BY student_id
) as max_scores ON s.id = max_scores.student_id
SET s.score = max_scores.max_score;
代码解读：
-
UPDATE student s: 指定更新student表，并使用别名s简化代码。 -
LEFT JOIN (...) as max_scores: 使用LEFT JOIN连接student表和一个子查询。子查询计算每个学生的最高分，并将其结果命名为max_scores。 -
SELECT student_id, MAX(score) as max_score FROM score GROUP BY student_id: 子查询根据student_id分组，并使用MAX(score)函数获取每个学生的最高分。 -
ON s.id = max_scores.student_id: 连接条件，将student表和子查询结果通过student_id关联起来。 -
SET s.score = max_scores.max_score: 将student表中每个学生的score更新为max_scores表中对应的max_score。
示例数据及结果：
假设初始数据如下：
student表：
| id | name | score |
|---|---|---|
| 1 | 小明 | 0 |
| 2 | 小红 | 0 |
score表：
| id | student_id | score |
|---|---|---|
| 1 | 1 | 80 |
| 2 | 1 | 88 |
| 3 | 2 | 95 |
| 4 | 2 | 98 |
执行上述UPDATE语句后，student表将被更新为：
| id | name | score |
|---|---|---|
| 1 | 小明 | 88 |
| 2 | 小红 | 98 |
此方法避免了使用循环或存储过程，效率更高，更符合SQL的编程范式。 它清晰地表达了更新逻辑，易于理解和维护。
