Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 20: все разновидности запросов на объединение в трёх СУБД
r_id |
r_name |
r_space |
|
TOP x … |
|
r_space_left |
1 |
Комната с двумя компьютерами |
5 |
|
TOP 2 |
|
2 |
2 |
Комната с тремя компьютерами |
5 |
|
TOP 3 |
|
3 |
3 |
Пустая комната 1 |
2 |
|
TOP 2 |
|
2 |
4 |
Пустая комната 2 |
2 |
|
TOP 2 |
|
2 |
5 |
Пустая комната 3 |
2 |
|
TOP 2 |
|
2 |
Решение для Oracle эквивалентно решению для MySQL за исключением использования функции NVL вместо функции IFNULL и способа нумерации свободных компьютеров с помощью функции ROW_NUMBER вместо использования инкрементируемой переменной.
Oracle Решение 2.2.10.m
1SELECT "r_id",
2"r_name",
3"r_space",
4( "r_space" - NVL("r_used", 0) ) AS "r_space_left",
5"c_id",
6"c_name"
7 |
|
FROM "rooms" |
|
|
8 |
|
LEFT JOIN (SELECT "c_room" |
AS "c_room_inner", |
|
9 |
|
|
COUNT("c_room") AS "r_used" |
|
10 |
|
FROM |
"computers" |
|
11 |
|
GROUP |
BY "c_room") "computers_in_room" |
|
12 |
|
ON "r_id" = |
"c_room_inner" |
|
13 |
|
CROSS JOIN (SELECT |
"c_id", |
|
14 |
|
|
"c_room", |
|
15 |
|
|
"c_name", |
|
16 |
|
|
ROW_NUMBER() OVER (ORDER BY "c_name" ASC) |
|
17 |
|
|
AS "position" |
|
18 |
|
FROM |
"computers" WHERE "c_room" IS NULL |
|
19 |
|
ORDER |
BY "c_name" ASC) "cross_apply_data" |
|
20WHERE "position" <= ("r_space" - NVL("r_used", 0))
21ORDER BY "r_id",
22"c_id"
Задача 2.2.10.n: показать расстановку компьютеров по непустым комнатам так, чтобы в выборку не попало больше компьютеров, чем может поместиться в комнату.
Ожидаемый результат 2.2.10.n.
r_id |
r_name |
r_space |
c_id |
c_room |
c_name |
1 |
Комната с двумя компьютерами |
5 |
1 |
1 |
Компьютер A в комнате 1 |
1 |
Комната с двумя компьютерами |
5 |
2 |
1 |
Компьютер B в комнате 1 |
2 |
Комната с тремя компьютерами |
5 |
3 |
2 |
Компьютер A в комнате 2 |
2 |
Комната с тремя компьютерами |
5 |
4 |
2 |
Компьютер B в комнате 2 |
2 |
Комната с тремя компьютерами |
5 |
5 |
2 |
Компьютер C в комнате 2 |
Решение 2.2.10.n: используем CROSS APPLY в MS SQL Server и эмуляцию аналогичного поведения в MySQL и Oracle.
Эта и следующая (2.2.10.o{179}) задачи являются самыми классическими случаями использования CROSS APPLY и OUTER APPLY: производится объединение таблиц по некоторому условию, а также учитывается дополнительное условие, данные для которого берутся из левой таблицы.
В нашем случае условием объединения является совпадение значений по-
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 175/545
Пример 20: все разновидности запросов на объединение в трёх СУБД
лей r_id и c_room, а дополнительным условием, ограничивающим выборку из правой таблицы, является вместимость комнаты, представленная в поле r_space.
Обратите внимание, что в эмуляции CROSS APPLY для MySQL и Oracle в данном случае используется внутреннее объединение (JOIN), а не декартово произведение (CROSS JOIN).
Решение для MySQL получается несколько громоздким по синтаксису, но очень простым по сути.
MySQL Решение 2.2.10.n
1SELECT `r_id`,
2`r_name`,
3`r_space`,
4`c_id`,
5`c_room`,
6`c_name`
7 |
|
FROM `rooms` |
|
8 |
|
JOIN (SELECT |
`c_id`, |
9 |
|
|
`c_room`, |
10 |
|
|
`c_name`, |
11 |
|
|
@row_num := IF(@prev_value = `c_room`, @row_num + 1, 1) |
12 |
|
|
AS `position`, |
13 |
|
|
@prev_value := `c_room` |
14 |
|
FROM |
`computers`, |
15 |
|
|
(SELECT @row_num := 1) AS `x`, |
16 |
|
|
(SELECT @prev_value := '') AS `y` |
17 |
|
ORDER |
BY `c_room`, |
18 |
|
|
`c_name` ASC) AS `cross_apply_data` |
|
|
|
|
19ON `r_id` = `c_room`
20WHERE `position` <= `r_space`
21ORDER BY `r_id`,
22`c_id`
Подзапрос в строках 8-18 возвращает список всех компьютеров с их нумерацией в контексте комнаты.
c_id |
c_room |
c_name |
position |
6 |
NULL |
Свободный компьютер A |
1 |
7 |
NULL |
Свободный компьютер B |
1 |
8 |
NULL |
Свободный компьютер C |
1 |
1 |
1 |
Компьютер A в комнате 1 |
1 |
2 |
1 |
Компьютер B в комнате 1 |
2 |
3 |
2 |
Компьютер A в комнате 2 |
1 |
4 |
2 |
Компьютер B в комнате 2 |
2 |
5 |
2 |
Компьютер C в комнате 2 |
3 |
В задачах 2.2.10.j{166}, 2.2.10.l{170} и 2.210.m{173} мы выполняли сквозную нумерацию компьютеров, не учитывая их расстановку по комнатам. Для декартового произведения (CROSS JOIN) нужен как раз такой сквозной номер, т.к. CROSS JOIN обрабатывает все записи из правой таблицы. Но для внутреннего объединения (JOIN) нужно как раз обратное — номер компьютера в контексте комнаты, в которой он расположен, т.к. JOIN будет искать соответствие между комнатами и расположенными в них компьютерами.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 176/545
Пример 20: все разновидности запросов на объединение в трёх СУБД
Результат выполнения JOIN (строка 8) выглядит так:
r_id |
r_name |
r_space |
c_id |
c_room |
c_name |
position |
1 |
Комната с двумя компьютерами |
5 |
1 |
1 |
Компьютер A в комнате 1 |
1 |
1 |
Комната с двумя компьютерами |
5 |
2 |
1 |
Компьютер B в комнате 1 |
2 |
2 |
Комната с тремя компьютерами |
5 |
3 |
2 |
Компьютер A в комнате 2 |
1 |
2 |
Комната с тремя компьютерами |
5 |
4 |
2 |
Компьютер B в комнате 2 |
2 |
2 |
Комната с тремя компьютерами |
5 |
5 |
2 |
Компьютер C в комнате 2 |
3 |
Условие WHERE `position` <= `r_space` в строке 20 гарантирует, что
в выборку не попадёт ни один компьютер, порядковый номер которого (в контексте комнаты, в которой он расположен) больше вместимости комнаты. Так получается итоговый результат.
MS SQL Решение 2.2.10.n
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 |
|
|
WHERE [c_room] = [r_id] |
|
14 |
|
|
ORDER |
BY [c_name] ASC) AS [cross_apply_data] |
15ORDER BY [r_id],
16[c_id]
ВMS SQL Server условие объединения указывается в строке 13 (WHERE [c_room] = [r_id]), а дополнительное условие (выбрать не больше компьютеров, чем вмещает комната) указывается в строке 8 (SELECT TOP ([r_space])).
Таким образом, правая часть CROSS APPLY в один приём получает полностью готовый набор данных: для каждой комнаты получается список её компьютеров, в котором позиций не больше, чем вместимость комнат:
c_id |
c_room |
c_name |
|
|
1 |
1 |
Компьютер A в комнате 1 |
Набор данных для комнаты 1 |
|
2 |
1 |
Компьютер B в комнате 1 |
||
|
||||
3 |
2 |
Компьютер A в комнате 2 |
|
|
4 |
2 |
Компьютер B в комнате 2 |
Набор данных для комнаты 2 |
|
5 |
2 |
Компьютер C в комнате 2 |
|
Остаётся только добавить к каждой строке этого набора информацию о соответствующей комнате (что и делает CROSS APPLY), и таким образом получается итоговый результат.
Решение для Oracle аналогично решению для MySQL, за исключением одной особенности нумерации компьютеров.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 177/545
Пример 20: все разновидности запросов на объединение в трёх СУБД
Oracle Решение 2.2.10.n
1SELECT "r_id",
2"r_name",
3"r_space",
4"c_id",
5"c_room",
6"c_name"
7 |
|
FROM "rooms" |
8 |
|
JOIN (SELECT "c_id", |
9 |
|
"c_room", |
10 |
|
"c_name", |
11 |
|
( CASE |
12 |
|
WHEN "c_room" IS NULL THEN 1 |
13 |
|
ELSE ROW_NUMBER() |
14 |
|
OVER ( |
15 |
|
PARTITION BY "c_room" |
16 |
|
ORDER BY "c_name" ASC) |
17 |
|
END ) AS "position" |
18 |
|
FROM "computers") "cross_apply_data" |
19ON "r_id" = "c_room"
20WHERE "position" <= "r_space"
21ORDER BY "r_id",
22"c_id"
Особенность нумерации состоит в том, как MySQL и Oracle нумеруют свободные компьютеры. MySQL для каждого свободного компьютера считает его «номер в комнате» равным 1 (что вполне логично). Этот эффект получается в силу логики условия @row_num := IF(@prev_value = `c_room`, @row_num + 1, 1): если предыдущее значение было NULL, и следующее — тоже NULL, то они не равны (NULL не равен сам себе). Итого MySQL получает:
c_id |
c_room |
c_name |
position |
6 |
NULL |
Свободный компьютер A |
1 |
7 |
NULL |
Свободный компьютер B |
1 |
8 |
NULL |
Свободный компьютер C |
1 |
1 |
1 |
Компьютер A в комнате 1 |
1 |
2 |
1 |
Компьютер B в комнате 1 |
2 |
3 |
2 |
Компьютер A в комнате 2 |
1 |
4 |
2 |
Компьютер B в комнате 2 |
2 |
5 |
2 |
Компьютер C в комнате 2 |
3 |
Oracle же учитывает в поведении функции ROW_NUMBER такую ситуацию и обрабатывает все NULL-значения как равные друг другу:
c_id |
c_room |
c_name |
position |
1 |
1 |
Компьютер A в комнате 1 |
1 |
2 |
1 |
Компьютер B в комнате 1 |
2 |
3 |
2 |
Компьютер A в комнате 2 |
1 |
4 |
2 |
Компьютер B в комнате 2 |
2 |
5 |
2 |
Компьютер C в комнате 2 |
3 |
6 |
NULL |
Свободный компьютер A |
1 |
7 |
NULL |
Свободный компьютер B |
2 |
8 |
NULL |
Свободный компьютер C |
3 |
Чтобы добиться от Oracle поведения, аналогичного поведению MySQL, мы используем выражение CASE (строки 11-17).
Для решения данной конкретной задачи эта особенность не важна (свободные компьютеры никак не учитываются, а потому их порядковый номер не важен), но знать и помнить о таком различии в поведении этих двух СУБД полезно.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 178/545
Пример 20: все разновидности запросов на объединение в трёх СУБД
Задача 2.2.10.o: показать расстановку компьютеров по всем комнатам так, чтобы в выборку не попало больше компьютеров, чем может поместиться в комнату.
Ожидаемый результат 2.2.10.o.
r_id |
r_name |
r_space |
c_id |
c_room |
c_name |
1 |
Комната с двумя компьютерами |
5 |
1 |
1 |
Компьютер A в комнате 1 |
1 |
Комната с двумя компьютерами |
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 |
NULL |
NULL |
NULL |
4 |
Пустая комната 2 |
2 |
NULL |
NULL |
NULL |
5 |
Пустая комната 3 |
2 |
NULL |
NULL |
NULL |
Решение 2.2.10.o: используем OUTER APPLY в MS SQL Server и эмуляцию аналогичного поведения в MySQL и Oracle.
Единственное отличие этой задачи от задачи 2.2.10.n{175} состоит в том, что нужно добавить в выборку пустые комнаты. Для MySQL и Oracle это делается за-
меной JOIN на LEFT JOIN, а для MS SQL Server — заменой CROSS APPLY на OUTER APPLY. Также в MySQL и Oracle нужно немного изменить условие, отвечающее за сравнение номера компьютера и вместимости комнаты.
MySQL Решение 2.2.10.n
1SELECT `r_id`,
2`r_name`,
3`r_space`,
4`c_id`,
5`c_room`,
6`c_name`
7 |
|
FROM `rooms` |
|
8 |
|
LEFT JOIN (SELECT `c_id`, |
|
9 |
|
|
`c_room`, |
10 |
|
|
`c_name`, |
11 |
|
|
@row_num := IF(@prev_value = `c_room`, @row_num + 1, 1) |
12 |
|
|
AS `position`, |
13 |
|
|
@prev_value := `c_room` |
14 |
|
FROM |
`computers`, |
15 |
|
|
(SELECT @row_num := 1) AS `x`, |
16 |
|
|
(SELECT @prev_value := '') AS `y` |
17 |
|
ORDER |
BY `c_room`, |
18 |
|
|
`c_name` ASC) AS `cross_apply_data` |
19ON `r_id` = `c_room`
20WHERE `position` <= `r_space` OR `position` IS NULL
21ORDER BY `r_id`,
22`c_id`
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 179/545