Sql 如何为缺少的类别添加行

这很接近，但我们也希望包括before名学生组合而不是VIA FULL JOIN.对于每个学生组合，FULL JOIN仍然有一个占位符行，但ID值将为空，因此需要提前合并.

我们还希望在最后的联接上更精确，一旦我们这样做了，我们就不再需要最后的DISTINCT:

WITH Subjects AS (
    SELECT DISTINCT Subject
    FROM Grades
    WHERE Subject IN ('English', 'Math')
)
, Terms AS (
    SELECT DISTINCT Year, Term
    FROM Grades
)
, Students AS (
    SELECT DISTINCT Student
    FROM Grades
)
,  Combined AS (
    SELECT *
    FROM Subjects
    CROSS JOIN Terms
    CROSS JOIN Students
)
SELECT c.Student, c.Year, c.Term, c.Subject, g.CourseTitle, g.Credit
FROM  Combined c
LEFT JOIN Grades g ON c.Term = g.Term AND c.Subject = g.Subject AND c.Student = g.Student

任何值得使用的SIS(学生信息系统)也将为您提供一种获取条款、课程和学生的方法，而无需查看每个注册记录.

最后，我怀疑你不需要学生/学期的每一种可能的组合，只需要学生实际参与的组合(即使是在数学和英语以外的科目).通过这种方式，毕业、离开或以其他方式离开学校的学生不会在他们离开后或到达之前污染学期的结果.

如果是这样的话，看起来是这样的:

WITH Subjects AS (
    SELECT DISTINCT Subject
    FROM Grades
    WHERE Subject IN ('English', 'Math')
)
, StudentTerms AS (
    SELECT DISTINCT Student, Year, Term
    FROM Grades
)
,  Combined AS (
    SELECT *
    FROM Subjects
    CROSS JOIN StudentTerms
)
SELECT c.Student, c.Year, c.Term, c.Subject, g.CourseTitle, g.Credit
FROM  Combined c
LEFT JOIN Grades g ON c.Term = g.Term AND c.Subject = g.Subject AND c.Student = g.Student