如何用MySQL查询每个学生的平均成绩？

Question

**问题描述：** 在学生管理系统中，存在一个学生表 `students` 和一个成绩表 `scores`，其中 `scores` 表中包含字段 `student_id` 和 `score`。现需查询每位学生的平均成绩，并列出平均分大于80分的学生。请说明如何使用 MySQL 编写对应的查询语句，并解释 `GROUP BY` 与 `HAVING` 的作用。

Answer 1

一、问题背景与基础查询

在学生管理系统中，我们通常会将学生的基本信息存储在 students 表中，而将学生的成绩信息存储在 scores 表中。两个表通过 student_id 字段进行关联。

现在的问题是：需要查询每位学生的平均成绩，并列出平均分大于80分的学生。

要实现这个需求，可以使用 SQL 的聚合函数 AVG() 来计算平均成绩，并结合 GROUP BY 和 HAVING 子句进行分组与筛选。

SELECT s.student_id, AVG(sc.score) AS average_score
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
GROUP BY s.student_id
HAVING average_score > 80;

二、GROUP BY 的作用详解

GROUP BY 是 SQL 中用于将结果集按照一个或多个列进行分组的关键字。在本例中，我们需要根据 student_id 对成绩进行分组，从而计算每个学生的平均成绩。

如果没有 GROUP BY，则 AVG() 函数将对整个表的 score 字段进行平均，而无法区分每个学生的个体情况。

例如，以下查询将只返回一个平均值，而不是每个学生的平均值：

SELECT AVG(score) AS overall_average FROM scores;

因此，GROUP BY 的作用是将数据按照指定字段进行分组，为后续的聚合运算提供基础。

三、HAVING 与 WHERE 的区别与应用场景

在 SQL 查询中，WHERE 和 HAVING 都用于筛选数据，但它们的应用场景不同：

• WHERE：在分组之前进行数据过滤，作用于原始数据。
• HAVING：在分组之后进行筛选，作用于聚合后的结果。

在本例中，我们需要筛选出平均成绩大于80分的学生，而平均成绩是通过 AVG() 函数在分组后计算得到的，因此必须使用 HAVING。

例如，以下查询是错误的：

SELECT s.student_id, AVG(sc.score) AS average_score
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
GROUP BY s.student_id
WHERE AVG(sc.score) > 80; -- 错误：WHERE 不能用于聚合函数

正确的做法是使用 HAVING：

SELECT s.student_id, AVG(sc.score) AS average_score
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
GROUP BY s.student_id
HAVING average_score > 80;

四、进阶：性能优化与索引建议

当数据量较大时，频繁的 JOIN 和聚合操作可能会影响查询性能。为了优化查询效率，可以考虑以下几点：

1. 为 scores 表中的 student_id 字段添加索引，加快连接操作。
2. 避免在 HAVING 中使用复杂的计算，尽量使用已经计算好的列。
3. 如果业务允许，可以考虑将平均分预计算并存储在 students 表中，减少实时计算压力。

示例索引创建语句：

CREATE INDEX idx_student_id ON scores(student_id);

五、扩展：使用子查询或视图实现更灵活的查询

为了增强查询的可读性和复用性，可以将平均分的计算封装为子查询或视图：

1. 使用子查询

SELECT s.student_id, avg_score
FROM students s
JOIN (
    SELECT student_id, AVG(score) AS avg_score
    FROM scores
    GROUP BY student_id
) AS avg_table ON s.student_id = avg_table.student_id
WHERE avg_table.avg_score > 80;

2. 创建视图

CREATE VIEW student_avg_scores AS
SELECT student_id, AVG(score) AS average_score
FROM scores
GROUP BY student_id;

然后可以像普通表一样使用视图进行查询：

SELECT s.student_id, sas.average_score
FROM students s
JOIN student_avg_scores sas ON s.student_id = sas.student_id
WHERE sas.average_score > 80;

六、总结与思考

本例展示了如何使用 GROUP BY 和 HAVING 实现对学生的平均成绩统计与筛选。这两个关键字在数据分析和报表生成中非常常见。

对于有 5 年以上经验的 IT 从业者来说，掌握这些 SQL 高级用法并理解其背后的执行机制，有助于编写高效、可维护的查询语句。

此外，结合索引优化、视图封装等手段，可以进一步提升系统的性能和可扩展性。