mysql - 在 MySQL 中向 UNION 添加条件

Question

我不确定我想做的事情是否可以通过联合实现，或者我是否需要使用嵌套查询和某种类型的连接。

select c1,c2 from t1
union
select c1,c2 from t2
// with some sort of condition where t1.c1 = t2.c1

例子:

t1
| 100 | regular |
| 200 | regular |
| 300 | regular |
| 400 | regular |

t2
| 100 | summer |
| 200 | summer |
| 500 | summer |
| 600 | summer |

Desired Result
| 100 | regular |
| 100 | summer  |
| 200 | regular |
| 200 | summer  |

我试过类似的东西:

select * from (select * from t1) as q1
inner join
    (select * from t2) as q2 on q1.c1 = q2.c1

但是这样将记录连接成一行:

| 100 | regular | 100 | summer |
| 200 | regular | 200 | summer |

Answer 1

尝试:

select c1, c2
  from t1
 where c1 in (select c1 from t2)
union all
select c1, c2
  from t2
 where c1 in (select c1 from t1)

根据编辑，尝试以下操作:

MySQL 没有允许您多次引用 t1 和 t2 sub 的 WITH 子句。您可能希望将 t1 和 t2 同时创建为数据库中的 View ，以便您可以在单个查询中多次将它们称为 t1 和 t2。

尽管如此，老实说，下面的查询看起来很糟糕，如果我们知道您的数据库结构，可能会进行很多优化。 IE。表格列表、每个表格的所有列及其数据类型、每个表格的一些示例行以及您的预期结果。

例如，在你的 t1 sub 中，你有一个与 LESSON 表的外部连接，但是你的 WHERE 子句中有标准(lesson.dayofweek >= 0)，这自然不允许空值，有效地转变你的外部连接进入内部连接。此外，您还有一些子查询，这些子查询仅使用标准检查 studentid 是否存在，这些标准表明实际上不需要使用多个表来产生您想要的结果。但是，在不知道您的数据库结构和一些具有预期结果的示例数据的情况下，很难提供进一步的建议。

即便如此，我相信下面的内容可能会满足您的需求，只是不是最优的。

select *
  from (select distinct students.student_number as "StudentID",
                        concat(students.first_name, ' ', students.last_name) as "Student",

                        general_program_types.general_program_name as "Program Category",
                        program_inventory.program_code as "Program Code",
                        std_lesson.studio_name as "Studio",
                        concat(teachers.first_name, ' ', teachers.last_name) as "Teacher",

          from lesson_student
          left join lesson
            on lesson_student.lesson_id = lesson.lesson_id
          left join lesson_summer
            on lesson_student.lesson_id = lesson_summer.lesson_id

         inner join students
            on lesson_student.student_number = students.student_number
         inner join studio as std_primary
            on students.primary_location_id = std_primary.studio_id
         inner join studio as std_lesson
            on (lesson.studio_id = std_lesson.studio_id or
               lesson_summer.studio_id = std_lesson.studio_id)

         inner join teachers
            on (lesson.teacher_id = teachers.teacher_id or
               lesson_summer.teacher_id = teachers.teacher_id)
         inner join lesson_program
            on lesson_student.lesson_id = lesson_program.lesson_id
         inner join program_inventory
            on lesson_program.program_code_id =
               program_inventory.program_code_id
         inner join general_program_types
            on program_inventory.general_program_id =
               general_program_types.general_program_id

         inner join accounts
            on students.ACCOUNT_NUMBER = accounts.ACCOUNT_NUMBER
         inner join account_contacts
            on students.ACCOUNT_NUMBER = account_contacts.ACCOUNT_NUMBER

        /** NOTE: the WHERE condition is the only **/
        /** difference between subquery1 & subquery2 **/
         where lesson.dayofweek >= 0 and
         order by students.STUDENT_NUMBER) t1
 where StudentID in
       (select StudentID
          from (select distinct students.student_number as "StudentID",
                                concat(students.first_name,
                                       ' ',
                                       students.last_name) as "Student",

                                general_program_types.general_program_name as "Program Category",
                                program_inventory.program_code as "Program Code",
                                std_lesson.studio_name as "Studio",
                                concat(teachers.first_name,
                                       ' ',
                                       teachers.last_name) as "Teacher",

                  from lesson_student
                  left join lesson
                    on lesson_student.lesson_id = lesson.lesson_id
                  left join lesson_summer
                    on lesson_student.lesson_id = lesson_summer.lesson_id

                 inner join students
                    on lesson_student.student_number =
                       students.student_number
                 inner join studio as std_primary
                    on students.primary_location_id = std_primary.studio_id
                 inner join studio as std_lesson
                    on (lesson.studio_id = std_lesson.studio_id or
                       lesson_summer.studio_id = std_lesson.studio_id)

                 inner join teachers
                    on (lesson.teacher_id = teachers.teacher_id or
                       lesson_summer.teacher_id = teachers.teacher_id)
                 inner join lesson_program
                    on lesson_student.lesson_id = lesson_program.lesson_id
                 inner join program_inventory
                    on lesson_program.program_code_id =
                       program_inventory.program_code_id
                 inner join general_program_types
                    on program_inventory.general_program_id =
                       general_program_types.general_program_id

                 inner join accounts
                    on students.ACCOUNT_NUMBER = accounts.ACCOUNT_NUMBER
                 inner join account_contacts
                    on students.ACCOUNT_NUMBER =
                       account_contacts.ACCOUNT_NUMBER

                /** NOTE: the WHERE condition is the only **/
                /** difference between subquery1 & subquery2 **/
                 where lesson_summer.dayofweek >= 0
                 order by students.STUDENT_NUMBER) t2)
UNION ALL
select *
  from (select distinct students.student_number as "StudentID",
                        concat(students.first_name, ' ', students.last_name) as "Student",

                        general_program_types.general_program_name as "Program Category",
                        program_inventory.program_code as "Program Code",
                        std_lesson.studio_name as "Studio",
                        concat(teachers.first_name, ' ', teachers.last_name) as "Teacher",

          from lesson_student
          left join lesson
            on lesson_student.lesson_id = lesson.lesson_id
          left join lesson_summer
            on lesson_student.lesson_id = lesson_summer.lesson_id

         inner join students
            on lesson_student.student_number = students.student_number
         inner join studio as std_primary
            on students.primary_location_id = std_primary.studio_id
         inner join studio as std_lesson
            on (lesson.studio_id = std_lesson.studio_id or
               lesson_summer.studio_id = std_lesson.studio_id)

         inner join teachers
            on (lesson.teacher_id = teachers.teacher_id or
               lesson_summer.teacher_id = teachers.teacher_id)
         inner join lesson_program
            on lesson_student.lesson_id = lesson_program.lesson_id
         inner join program_inventory
            on lesson_program.program_code_id =
               program_inventory.program_code_id
         inner join general_program_types
            on program_inventory.general_program_id =
               general_program_types.general_program_id

         inner join accounts
            on students.ACCOUNT_NUMBER = accounts.ACCOUNT_NUMBER
         inner join account_contacts
            on students.ACCOUNT_NUMBER = account_contacts.ACCOUNT_NUMBER

        /** NOTE: the WHERE condition is the only **/
        /** difference between subquery1 & subquery2 **/
         where lesson_summer.dayofweek >= 0
         order by students.STUDENT_NUMBER) x
 where StudentID in
       (select StudentID
          from (select distinct students.student_number as "StudentID",
                                concat(students.first_name,
                                       ' ',
                                       students.last_name) as "Student",

                                general_program_types.general_program_name as "Program Category",
                                program_inventory.program_code as "Program Code",
                                std_lesson.studio_name as "Studio",
                                concat(teachers.first_name,
                                       ' ',
                                       teachers.last_name) as "Teacher",

                  from lesson_student
                  left join lesson
                    on lesson_student.lesson_id = lesson.lesson_id
                  left join lesson_summer
                    on lesson_student.lesson_id = lesson_summer.lesson_id

                 inner join students
                    on lesson_student.student_number =
                       students.student_number
                 inner join studio as std_primary
                    on students.primary_location_id = std_primary.studio_id
                 inner join studio as std_lesson
                    on (lesson.studio_id = std_lesson.studio_id or
                       lesson_summer.studio_id = std_lesson.studio_id)

                 inner join teachers
                    on (lesson.teacher_id = teachers.teacher_id or
                       lesson_summer.teacher_id = teachers.teacher_id)
                 inner join lesson_program
                    on lesson_student.lesson_id = lesson_program.lesson_id
                 inner join program_inventory
                    on lesson_program.program_code_id =
                       program_inventory.program_code_id
                 inner join general_program_types
                    on program_inventory.general_program_id =
                       general_program_types.general_program_id

                 inner join accounts
                    on students.ACCOUNT_NUMBER = accounts.ACCOUNT_NUMBER
                 inner join account_contacts
                    on students.ACCOUNT_NUMBER =
                       account_contacts.ACCOUNT_NUMBER

                /** NOTE: the WHERE condition is the only **/
                /** difference between subquery1 & subquery2 **/
                 where lesson.dayofweek >= 0 and
                 order by students.STUDENT_NUMBER) x);