我不确定使用联合是否可以实现我想做的事情，或者我是否需要使用嵌套查询和某种形式的联接。
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 |
尝试：
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子对象。您可能希望将t1和t2都创建为数据库中的视图，以便在单个查询中可以多次将它们分别称为t1和t2。
即便如此，下面的查询实际上还是很糟糕的，如果我们知道您的数据库结构，可能会对其进行很多优化。就是 表的列表，每个表上的所有列及其数据类型，每个表中的一些示例行以及预期的结果。
例如，在您的t1子目录中，您具有使用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);
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句