Блог

Я попытался выполнить приведенный ниже пример с использованием соединений и объединения, но не добился успеха. Я ценю любую помощь.

У меня есть таблица студентов и 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      
 

Здесь используется ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ, потому что все имена субъектов существуют не во всех таблицах. Первый подзапрос извлекает запись о курсе, зачислении и заключении студента. Затем ВНУТРЕННЕЕ СОЕДИНЕНИЕ с таблицей учащихся в соответствии с ожидаемым результатом. Если нужна вся информация о студенте, то лучше присоединиться слева. В Запланированной/Зачисленной/Завершенной таблице один и тот же курс/Зачисление/Заключение не могут быть назначены несколько раз для конкретного студента. В качестве идентификатора студента и курса, необходимых для расчета, используйте две таблицы student_id и курс внутри COALESCE (), чтобы всегда возвращать НЕ НУЛЕВОЕ значение.

 -- 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_2014amp;fiddle=09d03c0b64d31c8ae5a3b91145b7b7e5

Похоже, что ваша модель данных несколько несовершенна в своем дизайне, я бы ожидал, что таблица Courses с каждым курсом будет связана Course_Id с каждым студентом для каждого типа.

Я не совсем понимаю ваши желаемые результаты, но, похоже, вам нужен полный список курсов для каждого студента и какие из них применимы в каждом конкретном случае.

Вы можете использовать CTE для построения таблицы истинности для всех курсов, которая затем соединяется со студентами, чтобы каждому студенту был представлен полный список курсов, а затем соединяется с 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
 

Попробуйте это (без вложенных запросов или 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<>скрипка