MySQL如何制作FULL OUTER

Question

我有 4 个表，如下所示。

表:类

ClassID     |   ClassSTD
--------------------------------
1           |   STD-1
2           |   STD-2
3           |   STD-3
4           |   STD-4

表格:部分

SectionId   |   SectionName | ClassId
--------------------------------------------
1           |   sec-A       | 1
2           |   sec-B       | 1
3           |   sec-C       | 1
4           |   sec-A       | 2
5           |   sec-B       | 2
6           |   sec-C       | 2
7           |   sec-A       | 3

表:主题

subjectId   |   subjectName
------------------------------------
1           |   Art
2           |   Music
3           |   Play

表 SubjectAllocationToClass

classId     |   sectionID           |   subjectId   | type
-----------------------------------------------------------------------
1(STD-1)        |   1(sec-A)            |   1(Art)      | main
1(STD-1)        |   2(sec-B)            |   1(Art)      | main
1(STD-1)        |   3(sec-C)            |   1(Art)      | optional
1(STD-1)        |   1(sec-A)            |   2(Music)    | main
1(STD-1)        |   2(sec-B)            |   2(Music)    | optional

上表“SubjectAllocationToClass”显示了两种类型的主题(主要和可选)在类(class)部分的分布情况。

需要所有类(class)而不考虑部分或主题分配无论主题分配如何，都需要所有部分

我尝试进行 Left JOIN、Right JOIN 但无法实现愿望。

如何从 SELECT 语句中获得以下结果？

    classSTD |  sectionName | Main subjectName   | Optional subjectName
    ------------------------------------------------------------------------
    STD-1    |  sec-A       | Art, Music         |
    STD-1    |  sec-B       | Art                |  Music
    STD-1    |  sec-C       |                    |  Art
    STD-2    |              |                    |
    STD-3    |  sec-A       |                    |
    STD-4    |              |                    |



select 
  ClassSTD as ClassSTD, 
  sectionname AS SectionName, 
  COALESCE(GROUP_CONCAT(CASE WHEN sac.type = 'main' THEN subjectName END), '')  as 'Main subjectname',
  COALESCE(GROUP_CONCAT(CASE WHEN sac.type = 'optional' THEN subjectName END), '')  as 'Optional subjectname'
FROM SubjectAllocationToClass sac
JOIN  Class c  ON c.classid = sac.classid
Left JOIN Section sc ON sc.sectionid = sac.sectionid
Left JOIN Subject sj ON  sj.subjectid = sac.subjectid
GROUP BY ClassSTD, SectionName;

Answer 1

不需要使用FULL OUTER JOIN，可以直接使用LEFT JOIN和SUBQUERY

试试这个:

SELECT C.ClassSTD, 
       COALESCE(A.SectionName, '') AS SectionName, 
       COALESCE(A.Mainsubjectname, '') AS 'Main subjectname',
       COALESCE(A.Optionalsubjectname, '') AS 'Optional subjectname'
FROM Class C
LEFT JOIN ( SELECT sc.classId AS classId, 
                   sc.sectionname AS SectionName, 
                   COALESCE(GROUP_CONCAT(CASE WHEN sac.type = 'main' THEN subjectName END), '')  AS Mainsubjectname,
                   COALESCE(GROUP_CONCAT(CASE WHEN sac.type = 'optional' THEN subjectName END), '')  AS Optionalsubjectname
            FROM Section sc
            LEFT JOIN SubjectAllocationToClass sac ON sc.sectionid = sac.sectionid
            LEFT JOIN SUBJECT sj ON  sj.subjectid = sac.subjectid
            GROUP BY sc.classId, sc.SectionName
          ) AS A ON C.classId = A.classId;

对于您的第二个查询::

SELECT C.ClassSTD, 
       COALESCE(A.SectionName, '') AS SectionName, 
       COALESCE(A.Mainsubjectname, '') AS 'Main subjectname',
       COALESCE(A.Optionalsubjectname, '') AS 'Optional subjectname'
FROM Class C
LEFT JOIN ( SELECT sc.classId AS classId, 
                   sc.sectionname AS SectionName, 
                   COALESCE(GROUP_CONCAT((CASE WHEN sac.type = 'main' THEN subjectName END) ORDER BY subjectName), '')  AS Mainsubjectname,
                   COALESCE(GROUP_CONCAT((CASE WHEN sac.type = 'optional' THEN subjectName END) ORDER BY subjectName), '')  AS Optionalsubjectname
            FROM Section sc
            LEFT JOIN SubjectAllocationToClass sac ON sc.sectionid = sac.sectionid
            LEFT JOIN SUBJECT sj ON  sj.subjectid = sac.subjectid
            GROUP BY sc.classId, sc.SectionName
          ) AS A ON C.classId = A.classId;