在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没有WITH子句，它允许您多次引用t1和t2 subs。您可能希望在数据库中同时创建t1和t2视图，以便在整个查询过程中将它们多次引用为t1和t2。

即使是这样，下面的查询看起来仍然很糟糕，如果我们知道您的数据库结构，可能会进行很多优化。即。表的列表、每个表上的所有列及其数据类型、每个表中的几个示例行以及预期的结果。

例如，在您的t1子中，您有一个带the表的外部联接，但是在WHERE子句(lesson.dayofweek >= 0)中有条件，它自然不允许空值，有效地将外部联接转换为内部连接。此外，您还有子查询，这些查询只使用建议使用的几个表不需要实际用于生成所需结果的标准来检查学习的存在。但是，如果不了解数据库结构和一些具有预期结果的示例数据，就很难提供进一步的建议。

即使是这样，我相信下面可能会得到你想要的，只是不是最优的。

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);

Answer 2

UNION是可以的。

您希望运行相同的联接两次。只不过第一次你在左边，第二次在右边。

SELECT t1.* FROM t1 JOIN t2 USING (c1)
UNION
SELECT t2.* FROM t1 JOIN t2 USING (c1)

当然，如果可能的话，您可以运行一个查询，并将左侧保存在内存中，显示右侧，然后在末尾对保存的左侧进行排队。它需要比游标更多的内存，但是运行查询的时间只需一半时间(实际上，由于磁盘和资源缓存，运行的内存要少一些)。

参见这里示例SQLFiddle。