时间戳

首页 > 解决方案 > 合并来自多个表的值而不重复它

sql - 合并来自多个表的值而不重复它

问题描述

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

我有一个学生表和其他 3 个计划完成的课程表，当前已注册和已结束的课程。对于这些课程表中的每一个，我都有 FK_Student 和课程名称。我确实喜欢将每个课程名称的所有结果“连接”在一行中，每个课程表作为列名。（同一课程可以在多个表中）参见下面的示例：

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

标签： sqlsql-server

解决方案

此处使用FULL OUTER JOIN是因为并非每个主题名称都存在于所有表中。第一个子查询检索学生明智的课程、注册和结论记录。然后根据预期输出与学生表进行INNER JOIN 。如果需要所有学生信息，那么LEFT JOIN会更好。在 Planed/Enrolled/Concluded 表中，相同的课程/注册/结论不能为特定学生分配多个时间。由于计算需要 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;

请从网址https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=09d03c0b64d31c8ae5a3b91145b7b7e5检查

推荐阅读