如何查询每门课程成绩均大于80分的学生姓名？

Question

如何使用SQL查询每门课程成绩均大于80分的学生姓名？常见问题在于误用WHERE条件直接筛选成绩大于80分，导致结果包含仅部分课程达标的学生。正确方法需结合GROUP BY对学生分组，利用HAVING子句配合聚合函数（如MIN或AVG）确保该生所有课程最低分也大于80分，从而准确筛选出符合条件的姓名。

Answer 1

如何使用SQL查询每门课程成绩均大于80分的学生姓名

在教育类数据分析中，一个常见但容易出错的需求是：找出所有课程成绩均高于80分的学生。许多开发者会误用WHERE子句直接过滤成绩字段，导致结果包含“部分课程达标”的学生，从而产生逻辑偏差。本文将从基础概念到高级优化，系统性地剖析该问题的正确解决方案。

1. 问题背景与数据模型设计

假设我们有如下表结构：

CREATE TABLE student_scores (
    student_id INT,
    student_name VARCHAR(50),
    course_name VARCHAR(50),
    score DECIMAL(5,2)
);

并插入不少于10行示例数据：

student_id	student_name	course_name	score
1	张伟	数学	85
1	张伟	英语	90
1	张伟	物理	78
2	李娜	数学	88
2	李娜	英语	82
2	李娜	物理	86
3	王强	数学	92
3	王强	英语	94
3	王强	物理	90
4	赵敏	数学	75
4	赵敏	英语	85
5	刘洋	数学	81
5	刘洋	英语	83

2. 常见错误：误用WHERE条件筛选

初学者常写出如下错误SQL：

SELECT student_name
FROM student_scores
WHERE score > 80;

此语句的问题在于：它仅筛选出单条记录中score > 80的数据，而不会判断某个学生是否所有课程都满足条件。例如，张伟有一门物理成绩为78，不应被包含，但上述查询仍会返回其名字两次（数学和英语），造成重复且错误的结果。

3. 正确思路：分组后使用HAVING子句控制聚合条件

要确保“每门课程成绩均大于80”，本质是要求每个学生的最低分（MIN(score)）也必须大于80。因此，正确的策略是：

1. 按学生姓名进行GROUP BY分组；
2. 使用HAVING子句限制聚合函数MIN(score) > 80；
3. 排除任何一门低于80分的学生。

正确SQL如下：

SELECT student_name
FROM student_scores
GROUP BY student_name
HAVING MIN(score) > 80;

执行结果将只返回：李娜 和 王强（假设李娜三门分别为88/82/86，王强全在90以上），而张伟因物理78被排除。

4. 深入分析：为何MIN函数是关键？

使用MIN(score) > 80等价于“所有课程成绩均大于80”。这是因为最小值若大于80，则其余值必然更大。这种转换将“全称量词”逻辑转化为聚合判断，是解决此类“全部满足”问题的核心技巧。

另一种思路是检查是否存在小于等于80的成绩：

SELECT student_name
FROM student_scores
GROUP BY student_name
HAVING SUM(CASE WHEN score <= 80 THEN 1 ELSE 0 END) = 0;

这种方法通过条件计数实现逻辑否定，适用于更复杂的多条件场景。

5. 扩展思考：性能优化与索引建议

当数据量庞大时，可考虑以下优化：

• 在(student_name, score)上建立复合索引以加速GROUP BY和聚合操作；
• 若表巨大，可先用窗口函数标记低分记录，再做反向排除；
• 对于实时分析系统，建议预计算每位学生的最低分并缓存。

以下是基于窗口函数的替代方案：

WITH scored_cte AS (
  SELECT 
    student_name,
    score,
    MIN(score) OVER (PARTITION BY student_name) AS min_score
  FROM student_scores
)
SELECT DISTINCT student_name
FROM scored_cte
WHERE min_score > 80;

6. 流程图：逻辑判断路径

graph TD A[开始查询] --> B{读取student_scores表} B --> C[按student_name分组] C --> D[计算每组MIN(score)] D --> E{MIN(score) > 80?} E -- 是 --> F[保留该学生] E -- 否 --> G[排除该学生] F --> H[输出结果] G --> H

该流程图清晰展示了从原始数据到最终筛选的决策路径，强调了聚合判断的关键作用。