Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 20: все разновидности запросов на объединение в трёх СУБД
MS SQL |
Решение 2.2.10.1 |
|
||
1 |
-- Вариант 1: без ключевого слова JOIN SELECT |
[r_id], |
||
2 |
|
[r_name], |
|
|
3 |
|
[c_id] , |
|
|
4 |
|
[c_room], |
|
|
5 |
|
[c_name] |
|
|
6 |
FROM |
[rooms], |
|
|
7 |
|
[computers] |
|
|
8 |
WHERE |
[r_id] != [c_room] |
|
|
9 |
|
|
|
|
1 |
-- Вариант 2: с ключевым словом JOIN |
|
||
2 |
|
|||
SELECT [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 I Решение 2.2.10.i |
|
|||
1 |
-- Вариант 1: без ключевого слова JOIN SELECT |
"r_id" |
||
2 |
|
"r_name", |
|
|
3 |
|
"c_id" |
|
|
4 |
|
"c_room", |
|
|
5 |
|
"c_name" |
|
|
6 |
FROM |
"rooms", |
|
|
7 |
|
"computers" |
|
|
8 |
WHERE |
"r_id" != "c_room" |
|
|
9 |
|
|
|
|
1 |
-- Вариант 2: с ключевым словом JOIN SELECT |
"r_id" |
||
2 |
||||
|
"r_name", |
|
||
3 |
|
|
||
|
"c_id" |
|
||
4 |
|
|
||
|
"c_room", |
|
||
5 |
|
|
||
|
"c_name" |
|
||
6 |
|
|
||
FROM |
"rooms" |
|
||
7 |
|
|||
|
CROSS JOIN "computers" |
|
||
8 |
|
|
||
WHERE |
"r id" != "c room" |
|
||
9 |
|
|||
|
|
|
||
|
|
|
|
|
При выполнении декартового произведения с исключением СУБД не допускает в результирующую выборку реально существующие пары записей из обеих таблиц, т.е. получает все возможные попарные комбинации кроме тех, которые реально существуют.
На этом с классическими вариантами объединений — всё.
Задачи на «неклассическое объединение» предполагают решение на основе либо специфичного для той или иной СУБД синтаксиса, либо дополнительных действий (как правило — подзапросов).
Многие задачи в этом подразделе обязаны своим возникновением существованию в 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 Стр: 175/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.
Решение 2.2.
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 Стр: 176/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
1 |
SELECT [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] |
14 |
ORDER |
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 Стр: 177/545
Пример 20: все разновидности запросов на объединение в трёх СУБД |
||||
|
|
|
||
Oracle і Решение 2.2.10.j |
|
|
||
1 |
SELECT "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" |
|
|
15 |
WHERE |
"position" <= "r_space" |
|
|
16 |
ORDER |
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 Стр: 178/545
Пример 20: все разновидности запросов на объединение в трёх СУБД
|
|
Решение 2.2.10.k |
|
|
|
|
1 |
SELECT '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 |
|
AS 'empty rooms' |
|
|
|
13 |
|
CROSS JOIN 'computers' |
|
|
|
14 |
WHERE |
'c_room' IS NULL |
|
|
|
MS SQL І Решение 2.2.10.k |
|
|
||
|
1 |
SELECT [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 [croom] |
|
|
10 |
|
|
FROM |
[computers] |
|
11 |
|
|
WHERE |
[c room] ISNOT NULL)) |
|
12 |
|
AS [empty_rooms] |
|
|
|
13 |
|
CROSS JOIN [computers] |
|
|
|
14 |
WHERE |
[c room] IS NULL |
|
|
|
|
|
|
||
|
Oracle I Решение 2.2.10.k |
|
|
||
1 |
SELECT "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" |
|
||
13 |
|
CROSS JOIN "computers" |
|
||
14 |
WHERE |
"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 Стр: 179/545