#mysql #sql
#mysql #sql
Вопрос:
Я не уверен, возможно ли то, что я хочу сделать, с объединением, или мне нужно использовать вложенный запрос и какое-то объединение.
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 |
Ответ №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 несколько раз. Возможно, вам захочется создать оба t1 и t2 в качестве представления в вашей базе данных, чтобы вы могли ссылаться на них как на t1 и t2 несколько раз в течение одного запроса.
Тем не менее, приведенный ниже запрос, честно говоря, выглядит очень плохо и, вероятно, мог бы быть значительно оптимизирован, если бы мы знали структуру вашей базы данных. Т.е. список таблиц, все столбцы в каждой таблице и их тип данных, несколько примеров строк из каждой и ваш ожидаемый результат.
Например, в вашем подразделении t1 у вас есть внешнее объединение с таблицей LESSON, но тогда у вас есть критерии в вашем предложении WHERE (урок.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 amp; 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 amp; 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 amp; 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 amp; subquery2 **/
where lesson.dayofweek >= 0 and
order by students.STUDENT_NUMBER) x);
Комментарии:
1. Я обновил свой вопрос, включив в него подзапросы, для которых t1 и t2 на самом деле являются псевдонимами. Когда я попробовал ваше решение, оно сообщило мне «Таблица <mydb>.t2 не существует. Я так понимаю, это какая-то проблема с областью псевдонимов таблиц. Имеет ли это смысл?
2. @Kevin замените t2 на любое другое имя таблицы. В вашем примере вы назвали это как t2
3. t1 и t2 — это не таблицы в моей базе данных, это псевдонимы, которые я создал для своих двух подзапросов.
4. Если вы измените свой пример, включив в него фактические таблицы и желаемый результат, я могу изменить ответ.
Ответ №2:
Чтобы сравнить значения из двух таблиц, вам нужно выполнить объединение. Первый способ, который я вижу, это выполнить 2 запроса с внутренним объединением и объединить их:
select t1.* from t1
inner join t2 using (c1)
union
select t2.* from t1
inner join t2 using (c1)
Однако мы можем сделать лучше: вы можете видеть, что я использую точно такой же запрос, вплоть до того, какие столбцы я выбираю в результирующем наборе, это пустая трата времени. Если бы мы могли просто дублировать все записи(1)? Ну, мы можем:
select * from t1
inner join t2 using (c1)
cross join (
select 0 as parity
union
select 1 as parity) dup
Теперь у нас есть все в формате double, с дополнительным столбцом для четности, нам просто нужно выбрать любой набор столбцов в этом:
-- broken query
select case parity when
0 then t1.*
1 then t2.*
end from t1
inner join t2 using (c1)
cross join (
select 0 as parity
union
select 1 as parity) dup
К сожалению, case выражение не позволяет возвращать несколько столбцов, поэтому нам нужно включить тест для всех из них. В вашем случае достаточно одного столбца:
select c1, case parity when
0 then t1.c2
1 then t2.c2
end from t1
inner join t2 using (c1)
cross join (
select 0 as parity
union
select 1 as parity) dup
order by c1
-- order by parity, c1
Используйте закомментированное значение order by вместо этого, чтобы получить результирующий набор для t1 first, а затем t2 .
Предостережение: код не протестирован.
(1) В ANSI SQL есть функция под названием CTE (я думаю, 99), которая позволяет выделить общий запрос и затем ссылаться на него во всем основном запросе, но, к сожалению, mysql ее не поддерживает.
Ответ №3:
UNION Это нормально.
Вы хотите выполнить одно и то же объединение дважды. За исключением того, что в первый раз вы получаете левую сторону, а во второй раз — правую.
SELECT t1.* FROM t1 JOIN t2 USING (c1)
UNION
SELECT t2.* FROM t1 JOIN t2 USING (c1)
Конечно, если это вообще возможно, вы могли бы выполнить один запрос и сохранить левую часть в памяти, отобразить правую часть, а затем поставить сохраненную левую часть в очередь в конце. Для этого требуется гораздо больше памяти, чем для курсора, но запрос выполняется в два раза быстрее (что-то меньшее на самом деле из-за кэширования диска и ресурсов).
Смотрите здесь пример SQLFiddle.