Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 20: все разновидности запросов на объединение в трёх СУБД
MS SQL Решение 2.2.10.i
1-- Вариант 1: без ключевого слова JOIN
2SELECT [r_id],
3[r_name],
4[c_id],
5[c_room],
6[c_name]
7 |
|
FROM |
[rooms], |
8 |
|
|
[computers] |
9 |
|
WHERE |
[r_id] != [c_room] |
|
|
|
|
1-- Вариант 2: с ключевым словом JOIN
2SELECT [r_id],
3[r_name],
4[c_id],
5[c_room],
6[c_name]
7 |
|
FROM |
[rooms] |
8 |
|
|
CROSS JOIN [computers] |
9 |
|
WHERE |
[r_id] != [c_room] |
Oracle Решение 2.2.10.i
1-- Вариант 1: без ключевого слова JOIN
2SELECT "r_id",
3"r_name",
4"c_id",
5"c_room",
6"c_name"
7 FROM "rooms",
8"computers"
9WHERE "r_id" != "c_room"
1-- Вариант 2: с ключевым словом JOIN
2SELECT "r_id",
3"r_name",
4"c_id",
5"c_room",
6"c_name"
7 FROM "rooms"
8CROSS JOIN "computers"
9WHERE "r_id" != "c_room"
При выполнении декартового произведения с исключением СУБД не допускает в результирующую выборку реально существующие пары записей из обеих таблиц, т.е. получает все возможные попарные комбинации кроме тех, которые реально существуют.
На этом с классическими вариантами объединений — всё.
Задачи на «неклассическое объединение» предполагают решение на основе либо специфичного для той или иной СУБД синтаксиса, либо дополнительных действий (как правило — подзапросов).
Многие задачи в этом подразделе обязаны своим возникновением существованию в MS SQL Server и Oracle (начиная с версии 12c) операторов CROSS APPLY и OUTER APPLY. Потому здесь и далее решение для MS SQL Server будет первичным, а решения для MySQL и Oracle будут построены через эмуляцию соответствующего поведения.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 165/545
Пример 20: все разновидности запросов на объединение в трёх СУБД
Задача 2.2.10.j: показать возможные варианты расстановки компьютеров по комнатам (учитывать вместимость комнат).
Ожидаемый результат 2.2.10.j.
r_id |
r_name |
r_space |
c_id |
c_room |
c_name |
1 |
Комната с двумя компьютерами |
5 |
1 |
1 |
Компьютер A в комнате 1 |
1 |
Комната с двумя компьютерами |
5 |
2 |
1 |
Компьютер B в комнате 1 |
1 |
Комната с двумя компьютерами |
5 |
3 |
2 |
Компьютер A в комнате 2 |
1 |
Комната с двумя компьютерами |
5 |
4 |
2 |
Компьютер B в комнате 2 |
1 |
Комната с двумя компьютерами |
5 |
5 |
2 |
Компьютер C в комнате 2 |
2 |
Комната с тремя компьютерами |
5 |
1 |
1 |
Компьютер A в комнате 1 |
2 |
Комната с тремя компьютерами |
5 |
2 |
1 |
Компьютер B в комнате 1 |
2 |
Комната с тремя компьютерами |
5 |
3 |
2 |
Компьютер A в комнате 2 |
2 |
Комната с тремя компьютерами |
5 |
4 |
2 |
Компьютер B в комнате 2 |
2 |
Комната с тремя компьютерами |
5 |
5 |
2 |
Компьютер C в комнате 2 |
3 |
Пустая комната 1 |
2 |
1 |
1 |
Компьютер A в комнате 1 |
3 |
Пустая комната 1 |
2 |
3 |
2 |
Компьютер A в комнате 2 |
4 |
Пустая комната 2 |
2 |
1 |
1 |
Компьютер A в комнате 1 |
4 |
Пустая комната 2 |
2 |
3 |
2 |
Компьютер A в комнате 2 |
5 |
Пустая комната 3 |
2 |
1 |
1 |
Компьютер A в комнате 1 |
5 |
Пустая комната 3 |
2 |
3 |
2 |
Компьютер A в комнате 2 |
Обратите внимание, что ни к одной комнате не было приписано компьютеров больше, чем значение в поле r_space.
Решение 2.2.10.j: используем CROSS APPLY в MS SQL Server и эмуляцию аналогичного поведения в MySQL и Oracle.
MySQL Решение 2.2.10.j
1SELECT `r_id`,
2`r_name`,
3`r_space`,
4`c_id`,
5`c_room`,
6`c_name`
7 |
|
FROM `rooms` |
|
8 |
|
CROSS JOIN (SELECT |
`c_id`, |
9 |
|
|
`c_room`, |
10 |
|
|
`c_name`, |
11 |
|
|
@row_num := @row_num + 1 AS `position` |
12 |
|
FROM |
`computers`, |
13 |
|
|
(SELECT @row_num := 0) AS `x` |
14 |
|
ORDER |
BY `c_name` ASC) AS `cross_apply_data` |
15WHERE `position` <= `r_space`
16ORDER BY `r_id`,
17`c_id`
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 166/545
Пример 20: все разновидности запросов на объединение в трёх СУБД
Подзапрос в строках 8-14 возвращает пронумерованный список компьюте-
ров:
c_id |
c_room |
c_name |
position |
1 |
1 |
Компьютер A в комнате 1 |
1 |
3 |
2 |
Компьютер A в комнате 2 |
2 |
2 |
1 |
Компьютер B в комнате 1 |
3 |
4 |
2 |
Компьютер B в комнате 2 |
4 |
5 |
2 |
Компьютер C в комнате 2 |
5 |
6 |
NULL |
Свободный компьютер A |
6 |
7 |
NULL |
Свободный компьютер B |
7 |
8 |
NULL |
Свободный компьютер C |
8 |
Условие в строке 15 позволяет исключить из итоговой выборки компьютеры с номерами, превышающими вместимость комнаты. Таким образом получается итоговый результат.
MS SQL Решение 2.2.10.j
1SELECT [r_id],
2[r_name],
3[r_space],
4[c_id],
5[c_room],
6[c_name]
7 |
|
FROM |
[rooms] |
|
8 |
|
|
CROSS APPLY (SELECT |
TOP ([r_space]) |
9 |
|
|
|
[c_id], |
10 |
|
|
|
[c_room], |
11 |
|
|
|
[c_name] |
12 |
|
|
FROM |
[computers] |
13 |
|
|
ORDER |
BY [c_name] ASC) AS [cross_apply_data] |
14ORDER BY [r_id],
15[c_id]
ВMS SQL Server оператор CROSS APPLY позволяет без никаких дополнительных действий обращаться из правой части запроса к данным из соответствующих строк левой части запроса. Благодаря этому конструкция SELECT TOP
([r_space]) ... приводит к выборке и подстановке из таблицы computers количества записей, не большего, чем значение r_space в соответствующей анализируемой строке из таблицы rooms. Поясним это графически:
r_id |
r_name |
r_space |
|
Что подставляется в TOP x |
|
1 |
Комната с двумя компьютерами |
5 |
|
SELECT TOP 5 ... |
|
2 |
Комната с тремя компьютерами |
5 |
|
SELECT TOP 5 ... |
|
3 |
Пустая комната 1 |
2 |
|
SELECT TOP 2 ... |
|
4 |
Пустая комната 2 |
2 |
|
SELECT TOP 2 ... |
|
5 |
Пустая комната 3 |
2 |
|
SELECT TOP 2 ... |
|
Благодаря такому поведению каждой строке из таблицы rooms подставляется определённое количество записей из таблицы computers, и так получается итоговый результат.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 167/545
Пример 20: все разновидности запросов на объединение в трёх СУБД
Oracle Решение 2.2.10.j
1SELECT "r_id",
2"r_name",
3"r_space",
4"c_id",
5"c_room",
6"c_name"
7 |
|
FROM "rooms" |
|
8 |
|
CROSS JOIN (SELECT |
"c_id", |
9 |
|
|
"c_room", |
10 |
|
|
"c_name", |
11 |
|
|
ROW_NUMBER() OVER (ORDER BY "c_name" ASC) |
12 |
|
|
AS "position" |
13 |
|
FROM |
"computers" |
14 |
|
ORDER |
BY "c_name" ASC) "cross_apply_data" |
|
|
|
|
15WHERE "position" <= "r_space"
16ORDER BY "r_id",
17"c_id"
Решение для Oracle эквивалентно решению для MySQL и отличается только способом нумерации компьютеров: здесь мы можем использовать готовую функ-
цию ROW_NUMBER.
Задача 2.2.10.k: показать возможные варианты расстановки свободных компьютеров по пустым комнатам (не учитывать вместимость комнат).
Ожидаемый результат 2.2.10.k.
r_id |
r_name |
c_id |
c_room |
c_name |
3 |
Пустая комната 1 |
6 |
NULL |
Свободный компьютер A |
3 |
Пустая комната 1 |
7 |
NULL |
Свободный компьютер B |
3 |
Пустая комната 1 |
8 |
NULL |
Свободный компьютер C |
4 |
Пустая комната 2 |
6 |
NULL |
Свободный компьютер A |
4 |
Пустая комната 2 |
7 |
NULL |
Свободный компьютер B |
4 |
Пустая комната 2 |
8 |
NULL |
Свободный компьютер C |
5 |
Пустая комната 3 |
6 |
NULL |
Свободный компьютер A |
5 |
Пустая комната 3 |
7 |
NULL |
Свободный компьютер B |
5 |
Пустая комната 3 |
8 |
NULL |
Свободный компьютер C |
Решение 2.2.10.k: используем перекрёстное объединение с некоторой предварительной подготовкой.
Единственная сложность этой задачи — в получении списка пустых комнат (т.к. свободные компьютеры мы элементарно определяем по значению NULL в поле c_room). Также эта задача отлично подходит для демонстрации одной типичной ошибки.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 168/545
Пример 20: все разновидности запросов на объединение в трёх СУБД
MySQL Решение 2.2.10.k
1SELECT `r_id`,
2`r_name`,
3`c_id`,
4`c_room`,
5`c_name`
6 |
|
FROM (SELECT |
`r_id`, |
|
7 |
|
|
`r_name` |
|
8 |
|
FROM |
`rooms` |
|
9 |
|
WHERE |
`r_id` NOT IN (SELECT |
DISTINCT `c_room` |
10 |
|
|
FROM |
`computers` |
11 |
|
|
WHERE |
`c_room` IS NOT NULL)) |
|
|
|
|
|
12AS `empty_rooms`
13CROSS JOIN `computers`
14 WHERE `c_room` IS NULL
MS SQL Решение 2.2.10.k
1SELECT [r_id],
2[r_name],
3[c_id],
4[c_room],
5[c_name]
6 |
|
FROM (SELECT |
[r_id], |
|
7 |
|
|
[r_name] |
|
8 |
|
FROM |
[rooms] |
|
9 |
|
WHERE |
[r_id] NOT IN (SELECT |
DISTINCT [c_room] |
10 |
|
|
FROM |
[computers] |
11 |
|
|
WHERE |
[c_room] IS NOT NULL)) |
|
|
|
|
|
12AS [empty_rooms]
13CROSS JOIN [computers]
14 WHERE [c_room] IS NULL
Oracle Решение 2.2.10.k
1SELECT "r_id",
2"r_name",
3"c_id",
4"c_room",
5"c_name"
6 |
|
FROM (SELECT |
"r_id", |
|
7 |
|
|
"r_name" |
|
8 |
|
FROM |
"rooms" |
|
9 |
|
WHERE |
"r_id" NOT IN (SELECT |
DISTINCT "c_room" |
10 |
|
|
FROM |
"computers" |
11 |
|
|
WHERE |
"c_room" IS NOT NULL)) |
12"empty_rooms"
13CROSS JOIN "computers"
14WHERE "c_room" IS NULL
Вэтом решении 14-я строка во всех трёх запросах отвечает за учёт только свободных компьютеров. Свободные комнаты определяются подзапросом в строках 6-12 (он возвращает список комнат, идентификаторы которых не встречаются в таблице computers):
r_id |
r_name |
3 |
Пустая комната 1 |
4 |
Пустая комната 2 |
5 |
Пустая комната 3 |
Очень частая типичная ошибка заключается в отсутствии условия WHERE c_room IS NOT NULL во внутреннем подзапросе в строках 9-11. Из-за этого в его результаты попадает NULL-значение, при обработке которого конструкция NOT IN возвращает FALSE для любого значения r_id, и в итоге подзапрос в строках 6-12 возвращает пустой результат.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 169/545