MySQL中如何将学生成绩行与GROUP BY统计结果合并查询？

在MySQL 8.0+中，利用窗口函数可以轻松实现这个需求，无需复杂子查询或多表关联，单条SQL即可完成：

SELECT
    id,
    date,
    student,
    subject,
    mark,
    MAX(mark) OVER (PARTITION BY date, subject) AS highest,
    MIN(mark) OVER (PARTITION BY date, subject) AS lowest,
    ROUND(AVG(mark) OVER (PARTITION BY date, subject), 2) AS avg_mark
FROM perf
ORDER BY date, subject, student;

关键说明： #

• PARTITION BY date, subject：将数据按日期+科目分组，每个分组内计算对应统计值
• MAX(mark) OVER (...)：在当前分组内取最高成绩，同理MIN取最低、AVG取平均，ROUND用于控制平均成绩的小数位数（可按需调整）
• 保留原表所有字段的同时附加统计字段，完全匹配需求

若需创建视图复用查询： #

如果后续需要多次调用该结果，可创建视图：

CREATE VIEW perf_with_class_stats AS
SELECT
    id,
    date,
    student,
    subject,
    mark,
    MAX(mark) OVER (PARTITION BY date, subject) AS highest,
    MIN(mark) OVER (PARTITION BY date, subject) AS lowest,
    ROUND(AVG(mark) OVER (PARTITION BY date, subject), 2) AS avg_mark
FROM perf;

使用时直接查询视图即可：

SELECT * FROM perf_with_class_stats ORDER BY date, subject, student;

内容的提问来源于stack exchange，提问作者user3245096