sql - 合并多个表中的值而不重复

Question

我尝试使用联接和联合来执行以下示例，但没有成功。感谢任何帮助。

我有一个学生表和另外3个计划要完成的类(class)表，当前已注册和已结束

Table: Student
Id_Student | Student
1 Bob
2 ...

Table: Planed
Id_Planed | Course | Fk_Student
1 History 1
2 English 1
3 Biology 1
4 Geometry 1
5 PE 1
6 Algebra 1
....

Table: Enrolled
Id_Enrolled | Enrollment | Fk_Student
1 History 1
2 Biology 1
3 PE 1
...

Table: Concluded
Id_Concluded | Conclusion | Fk_Student
1 History 1
2 English 1
3 Physics 1
...

Expected Result:
Student | Planed | Enrolled | Concluded
Bob History History History
Bob English NULL English 
Bob Biology Biology NULL 
Bob Geometry NULL NULL
Bob PE PE NULL
Bob Algebra NULL NULL
Bob NULL NULL Physics 

Answer 1

FULL OUTER JOIN 在这里使用是因为每个主题名称都不是在所有表中都存在的。第一个子查询检索学生明智的类(class)、注册和结论记录。然后 INNER JOIN 根据预期输出与学生表。如果需要所有学生信息，那么 LEFT JOIN 会更好。在计划/注册/结束表中，同一类(class)/注册/结论不能为特定学生分配多个时间。由于计算需要 student_id 和 course，因此在 COALESCE() 中使用两个表 student_id 和 course 以便始终返回 NOT NULL 值。

-- SQL SERVER (v2014)
SELECT s.Student, r.Course, r.Enrollment, r.Conclusion
FROM Student s
INNER JOIN (SELECT COALESCE(t.student_id, c.Fk_Student) student_id
                 , t.Course, t.Enrollment, c.Conclusion
            FROM (SELECT COALESCE(p.Fk_Student, e.Fk_Student) student_id
                       , COALESCE(p.Course, e.Enrollment) Course_t
                       , p.Course
                       , e.Enrollment
                  FROM Planed p
                  FULL OUTER JOIN Enrolled e
                               ON p.Fk_Student = e.Fk_Student
                              AND p.Course = e.Enrollment) t
                  FULL OUTER JOIN Concluded c
                               ON c.Fk_Student = t.student_id
                              AND c.Conclusion = t.Course_t) r
        ON s.Id_Student = r.student_id; 

请从 url https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=09d03c0b64d31c8ae5a3b91145b7b7e5 查看