如何用SQL查询个人平均成绩大于整体平均成绩的学号、姓名、平均分(保留1位小数)?

Question

如何用SQL查询个人平均成绩大于整体平均成绩的学号、姓名和平均分（保留1位小数）？ 在学生成绩管理场景中，我们常需要筛选出个人平均成绩高于所有学生平均成绩的学生信息。假设数据库包含两张表：学生表（students），字段有学号（id）、姓名（name）；成绩表（scores），字段有学号（student_id）、课程名（course）、成绩（score）。如何通过SQL实现这一需求？需先计算每位学生的平均成绩和个人平均成绩的整体均值，再筛选出符合条件的学生数据，并确保平均分保留1位小数。此问题涉及子查询、GROUP BY及聚合函数AVG的使用，具体SQL语句如何构建？

Answer 1

1. 问题背景与需求分析

在学生成绩管理场景中，筛选出个人平均成绩高于所有学生平均成绩的学生信息是一项常见需求。这需要我们从两张表（students 和 scores）中提取数据，并通过 SQL 查询实现以下目标：

• 计算每位学生的平均成绩。
• 计算所有学生平均成绩的整体均值。
• 筛选出个人平均成绩大于整体均值的学生。
• 确保输出的平均分保留1位小数。

为了解决这一问题，我们需要掌握 SQL 中的聚合函数（如 AVG）、GROUP BY 子句、子查询以及结果格式化等技术要点。

关键词：SQL 查询、AVG 函数、GROUP BY、子查询、数据格式化

2. 解决方案设计

以下是解决该问题的具体步骤：

1. 使用 GROUP BY 和 AVG 计算每位学生的平均成绩。
2. 通过子查询计算所有学生平均成绩的整体均值。
3. 将两部分结果结合，筛选出符合条件的学生。
4. 使用 ROUND 函数对平均分进行保留1位小数的格式化处理。

接下来，我们将具体构建 SQL 查询语句并逐步解析其逻辑。

3. SQL 查询语句构建

以下是完整的 SQL 查询语句：

SELECT 
    s.id AS 学号,
    s.name AS 姓名,
    ROUND(AVG(sc.score), 1) AS 平均分
FROM 
    students s
JOIN 
    scores sc ON s.id = sc.student_id
GROUP BY 
    s.id, s.name
HAVING 
    AVG(sc.score) > (
        SELECT 
            AVG(score)
        FROM 
            scores
    )
ORDER BY 
    平均分 DESC;

3.1 查询逻辑分解

上述查询可以分为以下几个部分：

1. 主查询部分：通过 JOIN 操作将 students 表和 scores 表连接起来，并按学号和姓名进行分组（GROUP BY），同时计算每位学生的平均成绩（AVG(sc.score)）。
2. 子查询部分：通过子查询计算所有学生平均成绩的整体均值（AVG(score)）。
3. HAVING 子句：用于筛选出个人平均成绩大于整体均值的学生。
4. ROUND 函数：对平均分进行保留1位小数的格式化处理。

4. 示例数据与结果展示

假设数据库中的数据如下：

students 表	scores 表
id name 1 张三 2 李四 3 王五	student_id course score 1 数学 85 1 英语 90 2 数学 70 2 英语 65 3 数学 95 3 英语 90

执行上述 SQL 查询后，结果如下：

学号	姓名	平均分
3	王五	92.5
1	张三	87.5

5. 流程图说明

以下是查询逻辑的流程图：

graph TD
    A[开始] --> B[从 students 和 scores 表中提取数据]
    B --> C[通过 JOIN 将两表关联]
    C --> D[按学号和姓名分组并计算个人平均成绩]
    D --> E[通过子查询计算整体平均成绩]
    E --> F[筛选出个人平均成绩大于整体均值的学生]
    F --> G[对平均分进行保留1位小数的格式化]
    G --> H[输出最终结果]

5.1 流程图解读

流程图清晰地展示了查询的执行步骤，从数据提取到最终结果输出，每一步都对应了 SQL 查询中的一个逻辑部分。