PostgreSQL JOIN查询时为缺失匹配行的成绩字段设置null的方法

核心实现思路 #

• 先提取去重的「学生-选课」关系列表，确定每个学生需要考核的课程范围
• 将选课关系与课程模块表关联，得到每个学生每门课下所有需要参加考试的模块全量列表
• 全量模块列表左关联成绩表，匹配到的返回实际成绩，未匹配到的缺考记录成绩自动为NULL，同时保留同一模块多次补考的所有成绩行

实现代码 #

WITH student_course AS (
    -- 提取所有学生的选课记录（去重避免重复生成模块行）
    SELECT DISTINCT student_id, course_id
    FROM student_evaluation
)
SELECT 
    sc.student_id,
    cm.module_id,
    sc.course_id,
    se.grade
FROM student_course sc
INNER JOIN course_module cm 
    ON sc.course_id = cm.course_id
LEFT JOIN student_evaluation se 
    ON sc.student_id = se.student_id 
    AND cm.module_id = se.module_id 
    AND sc.course_id = se.course_id
ORDER BY sc.student_id, cm.module_id;

方案说明 #

上述代码运行结果和你期望的输出完全一致，学生1课程1下的模块3无考试记录自动返回NULL，同一模块的多次补考成绩也会全部保留不会遗漏。
你之前用交叉连接/右连接不符合预期的核心原因是没有先锁定学生的选课范围：交叉连接会生成所有学生+所有课程模块的笛卡尔积，出现大量学生未选课程的无效模块行；右连接如果没有限定选课范围也会出现冗余的无效记录。

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