SQL多表无重复合并：实现学生多状态课程关联查询需求

实现方案 #

核心思路是先提取所有学生和对应课程的唯一全集，再通过左连接关联三张课程表补全对应列的内容。

可直接运行的SQL语句 #

SELECT 
    s.Student,
    p.Course AS Planed,
    e.Enrollment AS Enrolled,
    c.Conclusion AS Concluded
FROM (
    -- 合并三个课程表的学生+课程组合，自动去重得到全集
    SELECT Fk_Student, Course AS course_name FROM Planed
    UNION
    SELECT Fk_Student, Enrollment AS course_name FROM Enrolled
    UNION
    SELECT Fk_Student, Conclusion AS course_name FROM Concluded
) all_courses
-- 关联学生表获取学生姓名
INNER JOIN Student s ON all_courses.Fk_Student = s.Id_Student
-- 左连待修表，匹配到就显示课程名，否则为NULL
LEFT JOIN Planed p 
    ON all_courses.Fk_Student = p.Fk_Student 
    AND all_courses.course_name = p.Course
-- 左连在读表
LEFT JOIN Enrolled e 
    ON all_courses.Fk_Student = e.Fk_Student 
    AND all_courses.course_name = e.Enrollment
-- 左连已修表
LEFT JOIN Concluded c 
    ON all_courses.Fk_Student = c.Fk_Student 
    AND all_courses.course_name = c.Conclusion
ORDER BY s.Student, all_courses.course_name;

逻辑说明 #

1. 内层子查询用UNION合并三个课程表的学生外键+课程名数据，自动去重后得到每个学生对应的所有出现过的课程的唯一组合，避免同一学生的同一门课重复出现
2. 关联Student表拿到学生姓名，匹配的是学生主键和课程表的外键
3. 分别左连接三张课程表，只有学生ID和课程名同时匹配成功时才返回对应课程名，匹配失败返回NULL，完全符合预期的输出结构

内容的提问来源于stack exchange，提问作者Flib