SQL如何实现按科目计算最高分并填充到max字段

两种实现路径都存在，根据你是否需要将max字段值持久化存储在表中选择即可。

你之前写的语句问题在于分组维度错误：你需要按科目subject_id分组计算最高分，而非按学生student_id分组，再通过关联把科目最高分匹配到对应科目的所有记录上即可。这种方式不需要修改表内存储的数据，不需要额外维护字段，每次查询都能拿到实时准确的结果，没有数据不一致的风险。
对应查询语句如下：

SELECT 
    r.student_id,
    r.subject_id,
    r.total,
    sub.max_score AS `max`
FROM results r
INNER JOIN (
    SELECT 
        subject_id,
        MAX(total) AS max_score
    FROM results
    GROUP BY subject_id
) sub ON r.subject_id = sub.subject_id;

执行后就能直接得到你期望的展示效果，完全不需要提前给max字段填值。

如果你的业务要求必须把科目最高分实际存储在max列中（比如有特殊的索引、性能优化需求），普通查询无法自动在插入/更新时把值写入表中，必须通过触发器实现。
注意：这种存储冗余值的方案天然有一致性风险，除非明确必要否则不推荐使用。
实现步骤如下（以MySQL语法为例）：

• 先补全存量数据的max值

UPDATE results r
INNER JOIN (
    SELECT subject_id, MAX(total) AS max_score
    FROM results
    GROUP BY subject_id
) sub ON r.subject_id = sub.subject_id
SET r.`max` = sub.max_score;

• 创建插入后触发的触发器，新增记录时自动更新对应科目的所有记录的max值

DELIMITER //
CREATE TRIGGER trg_results_after_insert
AFTER INSERT ON results
FOR EACH ROW
BEGIN
    UPDATE results
    SET `max` = (SELECT MAX(total) FROM results WHERE subject_id = NEW.subject_id)
    WHERE subject_id = NEW.subject_id;
END //
DELIMITER ;

• 创建更新后触发的触发器，修改记录时同步更新相关科目的max值，覆盖科目变更、分数变更两种场景

DELIMITER //
CREATE TRIGGER trg_results_after_update
AFTER UPDATE ON results
FOR EACH ROW
BEGIN
    -- 如果修改了记录所属科目，需要同时更新旧科目、新科目的最高分
    IF OLD.subject_id <> NEW.subject_id THEN
        UPDATE results
        SET `max` = (SELECT MAX(total) FROM results WHERE subject_id = OLD.subject_id)
        WHERE subject_id = OLD.subject_id;
    END IF;

    UPDATE results
    SET `max` = (SELECT MAX(total) FROM results WHERE subject_id = NEW.subject_id)
    WHERE subject_id = NEW.subject_id;
END //
DELIMITER ;

• （可选）创建删除后触发的触发器，避免删除最高分记录后max值不准确

DELIMITER //
CREATE TRIGGER trg_results_after_delete
AFTER DELETE ON results
FOR EACH ROW
BEGIN
    UPDATE results
    SET `max` = (SELECT MAX(total) FROM results WHERE subject_id = OLD.subject_id)
    WHERE subject_id = OLD.subject_id;
END //
DELIMITER ;

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