Moodle数据库SQL透视查询：返回各grade_code的最高成绩

要让透视查询返回每个grade_code对应的最高成绩，核心是先对每个grade_code的成绩做最大值聚合，再执行透视，或者在透视的聚合逻辑中直接使用MAX()函数。以下是具体实现方案：

方案1：先聚合再透视（推荐） #

先通过分组查询，为每个用户（如果按用户维度统计）+ grade_code保留最高成绩，再基于这个结果集做透视：

SELECT *
FROM (
    -- 子查询：提取每个用户每个grade_code的最高成绩
    SELECT 
        userid, -- Moodle用户ID字段
        quiz AS grade_code, -- 假设quiz字段对应你的grade_code
        MAX(grade) AS max_grade -- 取该grade_code下的最高成绩
    FROM mdl_quiz_grades -- Moodle测验成绩表
    GROUP BY userid, quiz
) AS SourceData
PIVOT (
    MAX(max_grade) -- 透视时再次用MAX确保取最大值（避免分组遗漏）
    FOR grade_code IN ([148], [414]) -- 替换为你需要的grade_code列表
) AS PivotResult

方案2：直接在透视聚合中使用MAX #

如果你的原始透视查询没有提前聚合，只需将透视中的聚合函数从默认的SUM()（或其他）改为MAX()即可：

SELECT *
FROM (
    SELECT 
        userid,
        quiz AS grade_code,
        grade
    FROM mdl_quiz_grades
) AS SourceData
PIVOT (
    MAX(grade) -- 关键：用MAX代替原聚合函数，直接取每个grade_code的最高成绩
    FOR grade_code IN ([148], [414])
) AS PivotResult

为什么之前的ORDER BY无效？ #

ORDER BY仅控制结果集的排序顺序，不会改变透视时的聚合逻辑。如果之前的透视使用了SUM()或AVG()这类聚合函数，或者没有提前过滤重复grade_code的成绩，ORDER BY无法让数据库自动选择最大值。只有通过聚合函数MAX()明确指定取最大值，才能得到你需要的结果。

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