Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 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 вместо использования инкрементируемой переменной.
Oracl |
і Решение 2.2.10.m | |
|
|
|
|
e |
|
|
|
||
|
|
|
|
|
|
1 |
SELECT "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" |
||
20 |
WHERE "position" <= |
"r space" - NVL "r used", 0)) |
|||
21 |
ORDER BY "r id", |
|
|
|
|
22 |
|
"c id" |
|
|
|
Задача 2.2.10. п: показать расстановку компьютеров по непустым комнатам так, чтобы в выборку не попало больше компьютеров, чем может поместиться в комнату.
Ожидаемый результат 2.2.10.п.
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.п: используем CROSS APPLY в MS SQL Server и эмуляцию A/i аналогичного поведения в MySQL и Oracle.
Эта и следующая (2.2.10.o{179}) задачи являются самыми классическими случаями использования CROSS APPLY и OUTER APPLY: производится объединение
таблиц по некоторому условию, а также учитывается дополнительное условие, данные для которого берутся из левой таблицы.
В нашем случае условием объединения является совпадение значений по
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 185/545
Пример 20: все разновидности запросов на объединение в трёх СУБД
лей r_id и c_room, а дополнительным условием, ограничивающим выборку из правой таблицы, является вместимость комнаты, представленная в поле r_space.
Обратите внимание, что в эмуляции CROSS APPLY для MySQL и Oracle в данном случае используется внутреннее объединение (JOIN), а не декартово произведение (CROSS JOIN).
Решение для MySQL получается несколько громоздким по синтаксису, но очень простым по сути.
1 |
SELECT '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, |
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' |
19 |
|
ON '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 Стр: 186/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
1 |
SELECT [r_id], |
|
|
2 |
|
[r_name] |
|
3 |
|
[r_space], |
|
4 |
|
[c_id], |
|
5 |
|
[c_room] |
|
6 |
|
[c_name] |
|
|
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] |
|
15 |
ORDER 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 Стр: 187/545
Пример 20: все разновидности запросов на объединение в трёх СУБД
Oracle I Решение 2.2.10.П
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 Стр: 188/545
