SQLite中如何为Student表实现预计算的派生属性average_grade与total_credits？

方法一：手动批量更新（最适配你的场景） #

既然你提到数据库仅在学期末或出成绩时才会有数据变更，完全不需要自动触发逻辑，直接在每次数据操作完成后手动执行一次更新语句即可——这是最简洁、维护成本最低的方案。

具体SQL示例：

-- 批量更新Student表的平均成绩与总学分
UPDATE Student
SET
    total_credits = (
        SELECT SUM(C.credit)
        FROM Enrollment E
        JOIN Course C ON E.course_id = C.id
        WHERE E.student_id = Student.id
    ),
    average_grade = (
        SELECT AVG(E.grade)
        FROM Enrollment E
        WHERE E.student_id = Student.id
    );

每次完成Enrollment表的成绩录入/修改、或是Course表的学分调整后，执行一遍这条语句就能完成所有学生缓存字段的同步，逻辑直白易懂。

方法二：简化版触发器（适配零散变更场景） #

如果偶尔存在零散的成绩补录/修改需求，不想每次手动跑更新语句，可以用触发器实现自动维护。由于你的数据变更频率极低，不需要复杂逻辑，仅针对关键表的变更事件写触发规则即可。

步骤1：给Student表添加目标字段 #

ALTER TABLE Student ADD COLUMN average_grade REAL;
ALTER TABLE Student ADD COLUMN total_credits INTEGER;

步骤2：创建触发器 #

先针对Enrollment表的增删改操作，触发对应学生的缓存字段更新：

-- 插入选课/成绩时更新
CREATE TRIGGER UpdateStudentStatsAfterInsert AFTER INSERT ON Enrollment
BEGIN
    UPDATE Student
    SET
        total_credits = (
            SELECT SUM(C.credit)
            FROM Enrollment E
            JOIN Course C ON E.course_id = C.id
            WHERE E.student_id = NEW.student_id
        ),
        average_grade = (
            SELECT AVG(E.grade)
            FROM Enrollment E
            WHERE E.student_id = NEW.student_id
        )
    WHERE id = NEW.student_id;
END;

-- 修改成绩时更新
CREATE TRIGGER UpdateStudentStatsAfterUpdate AFTER UPDATE ON Enrollment
BEGIN
    UPDATE Student
    SET
        total_credits = (
            SELECT SUM(C.credit)
            FROM Enrollment E
            JOIN Course C ON E.course_id = C.id
            WHERE E.student_id = NEW.student_id
        ),
        average_grade = (
            SELECT AVG(E.grade)
            FROM Enrollment E
            WHERE E.student_id = NEW.student_id
        )
    WHERE id = NEW.student_id;
END;

-- 删除选课记录时更新
CREATE TRIGGER UpdateStudentStatsAfterDelete AFTER DELETE ON Enrollment
BEGIN
    UPDATE Student
    SET
        total_credits = (
            SELECT SUM(C.credit)
            FROM Enrollment E
            JOIN Course C ON E.course_id = C.id
            WHERE E.student_id = OLD.student_id
        ),
        average_grade = (
            SELECT AVG(E.grade)
            FROM Enrollment E
            WHERE E.student_id = OLD.student_id
        )
    WHERE id = OLD.student_id;
END;

如果Course表的学分存在调整可能，再补充一个针对Course表的触发器：

-- 课程学分变更时，更新选了该课的学生总学分
CREATE TRIGGER UpdateStudentStatsOnCourseCreditChange AFTER UPDATE OF credit ON Course
BEGIN
    UPDATE Student
    SET
        total_credits = (
            SELECT SUM(C.credit)
            FROM Enrollment E
            JOIN Course C ON E.course_id = C.id
            WHERE E.student_id = Student.id
        )
    WHERE id IN (SELECT student_id FROM Enrollment WHERE course_id = NEW.id);
END;

这套触发器逻辑直白，因为数据变更频率低，不会带来性能负担，还能自动维护缓存字段的准确性。

额外说明：为什么不推荐视图？ #

视图每次查询都需要实时关联多表计算，如果你频繁查询学生的平均成绩和总学分，确实会有性能损耗；但如果查询频率也不高，视图也是可选方案——不过结合你担心性能的需求，预计算缓存显然更合适。

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