如何为GRADES表中缺失的数学、英语科目添加行记录？

要实现需求，核心是先锁定每个学生有选课记录的学期（即学生+年份+学期的唯一组合），再为这些学期补全英语、数学的默认记录，最后合并原有非英数科目记录。以下是可行的SQL语句：

WITH StudentTerms AS (
    -- 获取所有学生有选课记录的（学生+年份+学期）组合
    SELECT DISTINCT Student, Year, Term
    FROM GRADES
),
RequiredSubjects AS (
    -- 定义需要补全的目标科目
    SELECT '英语' AS Subject UNION ALL
    SELECT '数学' AS Subject
),
StudentTermSubjects AS (
    -- 生成每个学生每个有记录的学期对应的英数科目行
    SELECT st.Student, st.Year, st.Term, rs.Subject
    FROM StudentTerms st
    CROSS JOIN RequiredSubjects rs
)
-- 合并补全的英数记录和原表的非英数记录
SELECT 
    sts.Student,
    sts.Year,
    sts.Term,
    g.CourseTitle,
    sts.Subject,
    COALESCE(g.Credit, 0) AS Credit
FROM StudentTermSubjects sts
LEFT JOIN GRADES g 
    ON sts.Student = g.Student 
    AND sts.Year = g.Year 
    AND sts.Term = g.Term 
    AND sts.Subject = g.Subject

UNION ALL

-- 保留原表中不属于英语、数学的科目记录
SELECT 
    Student, Year, Term, CourseTitle, Subject, Credit
FROM GRADES
WHERE Subject NOT IN ('英语', '数学')

-- 按学生、年份、学期、科目排序，匹配期望结果的顺序
ORDER BY Student, Year, Term, Subject;

关键逻辑说明 #

• StudentTerms：筛选出所有学生有选课记录的学期维度，确保只在学生有学分的学期补全英数记录，符合需求中「未获得任何学分则无需生成」的要求。
• RequiredSubjects：明确需要补全的两个科目，避免依赖原表数据（如果原表某学生从未选过英数，原写法会漏掉）。
• StudentTermSubjects：通过交叉连接，为每个学生的每个目标学期生成英数两行基础记录。
• 左连接原表：匹配已有选课记录，未匹配到的记录自动用COALESCE将学分设为0，课程名称保持NULL。
• UNION ALL合并非英数记录：保留原表中体育、科学等其他科目的原始数据，避免丢失。

执行后会生成完全符合期望的结果，包括补全的0学分英数记录，以及完整保留的其他科目数据。

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