sql - 为缺失的行添加默认设置(学生和科目组合)

我有一张桌子，上面有一些科目的学生成绩。
STUDENT_GRADES

id | studentid | subjectid | grade
-----------------------------------
1  |  1        | 1         | A+
2  |  1        | 2         | A
3  |  1        | 3         | A-
4  |  2        | 2         | A
5  |  2        | 3         | A-
6  |  3        | 2         | B
7  |  4        | 3         | B+

我还有一张桌子，上面有主题列表。
SUBJECTS

subjectid | subjectname
-----------------------------
1         | Maths
2         | Science
3         | History

现在我必须以以下格式检索结果。
FORMATTED_GRADES

studentid | subjectid | grade
-----------------------------------
1         | 1         | A+
1         | 2         | A
1         | 3         | A-

2         | 1         | fail
2         | 2         | A
2         | 3         | A-

3         | 1         | fail
3         | 2         | B
3         | 3         | fail

4         | 1         | fail
4         | 2         | fail
4         | 3         | B+

FORMATTED_GRADES包含每个来自STUDENT_GRADES的学生及其在每个科目中的成绩。如果在STUDENT_GRADES中没有某个科目的学生成绩，则该科目的学生成绩应为不及格。

最佳答案

使用cross join生成行，使用left join和coalesce()获取所需的数据：

select st.student_id, su.subject_id,
       coalesce(sg.grade, 'fail') as grade
from (select distinct student_id from student_grades) st cross join
     subjects su left join
     student_grades sg
     on sg.student_id = st.student_id and sg.subject_id = su.subject_id