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

Question

我尝试使用连接和联合做下面的例子，但没有成功。谢谢你的帮助。

我有一个Student课程表已规划当前已注册,结论课程。对于每个课程表，我都有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      

Answer 1

FULL OUTER JOIN之所以在这里使用，是因为所有表中都不存在每个主题名。第一个子查询检索学生的课程、注册和结业记录。ThenINNER JOIN根据预期输出使用学生表。如果需要所有学生信息，则LEFT JOIN会更好的。在计划/注册/结业表中，同一课程/注册/结业不能为特定学生分配多个时间。由于需要计算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查看

Answer 2

看起来你的数据模型在设计上有些缺陷，我本来希望有一个Courses表，每个课程由Course_Id链接到每个类型的每个学生。

我不清楚你想要的输出，但似乎你想要每个学生的完整课程列表，以及每种情况下哪些课程适用。

你可以使用CTE为所有课程建立一个真值表，然后交叉连接到student，这样每个学生都可以看到完整的课程列表，然后外部连接到3个表，以指示哪些课程适用于每种情况的学生。

with courses as (
select course from planed union
select enrollment from enrolled union
select conclusion from concluded
)
select s.Student, p.Course Planed, e.Enrollment Enrolled, cc.Conclusion Concluded
from courses c
cross join student s 
left join planed p on p.course=c.course and p.fk_student=s.id_student
left join enrolled e on e.enrollment=c.course and e.fk_student=s.id_student
left join concluded cc on cc.Conclusion=c.course and cc.fk_student=s.id_student

Answer 3

试试这个(没有嵌套查询或cte):

SELECT
s.Student,
p.Course,
e.Enrollment,
c.Conclusion
FROM Planed AS p
FULL JOIN Enrolled AS e
ON e.Fk_Student = p.Fk_Student AND
e.Enrollment = p.Course
FULL JOIN Concluded AS c
ON (c.Fk_Student = p.Fk_Student AND
c.Conclusion = p.Course) OR
(c.Fk_Student = e.Fk_Student AND
c.Conclusion = e.Enrollment)
RIGHT JOIN Student AS s
ON s.Id_Student IN (
p.Fk_Student,
e.Fk_Student,
c.Fk_Student
);

结果:

+---------+----------+------------+------------+
| Student |  Course  | Enrollment | Conclusion |
+---------+----------+------------+------------+
| Bob     | History  | History    | History    |
| Bob     | English  |            | English    |
| Bob     | Biology  | Biology    |            |
| Bob     | Geometry |            |            |
| Bob     | PE       | PE         |            |
| Bob     | Algebra  |            |            |
| Bob     |          |            | Physics    |
| Sam     |          |            |            |
+---------+----------+------------+------------+

,db&lt的在小提琴