Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 20: все разновидности запросов на объединение в трёх СУБД
Объединение с пустым результатом тоже даёт пустой результат. Так изза одного неочевидного условия весь запрос перестаёт возвращать какие бы то ни было данные.
Таким образом ведёт себя именно NOT IN. Просто IN работает ожидаемым образом, т.е. возвращает TRUE для входящих в анализируемое множество значений и FALSE для не входящих.
Задача 2.2.10.1: показать возможные варианты расстановки свободных компьютеров по пустым комнатам (учитывать вместимость комнат).
Ожидаемый результат
2.2.10.1.
r id |
r_name |
r_space |
c_id |
c_room |
c_name |
3 |
Пустая комната 1 |
2 |
6 |
NULL |
Свободный компьютер A |
3 |
Пустая комната 1 |
2 |
7 |
NULL |
Свободный компьютер B |
4 |
Пустая комната 2 |
2 |
6 |
NULL |
Свободный компьютер A |
4 |
Пустая комната 2 |
2 |
7 |
NULL |
Свободный компьютер B |
5 |
Пустая комната 3 |
2 |
6 |
NULL |
Свободный компьютер A |
5 |
Пустая комната 3 |
2 |
7 |
NULL |
Свободный компьютер B |
Решение 2.2.10.1: используем CROSS APPLY в MS SQL Server и эмуляцию аналогичного поведения в MySQL и Oracle.
Решение этой задачи сводится к комбинации решений задач 2.2.10.j{166} и 2.2.10. k{168}: из первой мы возьмём логику CROSS APPLY, из второй — логику получения списка пустых комнат.
SELECT 'r_id',
2'r_name',
3'r_space'
4'c_id',
5'c_room',
6'c name' FROM (SELECT 'r_id',
8 |
'r_name', |
9 |
'r_space' |
10FROM 'rooms'
11WHERE! 'r_id' NOT iN (SELECT 'c room'
12 |
FROM |
'computers' |
|
13 |
WHERE |
'c_room' IS |
NOT NULL)) |
14AS 'empty_rooms'
15CROSSJOIN (SELECT 'c_id',
16 |
|
'c_room', |
17 |
|
'c_name', |
18 |
|
@row_num := @row_num + 1 AS 'position' |
19 |
FROM |
'computers' , |
20 |
|
(SELECT @row num := 0) AS 'x' |
21 |
WHERE |
'c_room' IS NULL |
22 |
ORDER |
BY 'c_name' ASC) |
23 |
AS 'cros s_apply_data' |
|
24 |
WHERE 'position' <= 'r_space' |
|
25 |
ORDER BY ' id', |
|
26 |
'cid' |
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 180/545
Пример 20: все разновидности запросов на объединение в трёх СУБД
Подзапрос в строках 8-14 возвращает список пустых комнат:
r_id |
r_name |
r_space |
3 |
Пустая комната 1 |
2 |
4 |
Пустая комната 2 |
2 |
5 |
Пустая комната 3 |
2 |
Подзапрос в строках 15-23 возвращает пронумерованный список свободных компьютеров:
c_id |
c_room |
c_name |
position |
6 |
NULL |
Свободный компьютер A |
1 |
7 |
NULL |
Свободный компьютер B |
2 |
8 |
NULL |
Свободный компьютер C |
3 |
CROSS JOIN этих двух результатов даёт следующее декартово произведение (поле position добавлено для наглядности):
r_id |
r_name |
r_space |
c_id |
c_room |
c_name |
position |
3 |
Пустая комната 1 |
2 |
6 |
NULL |
Свободный компьютер A |
1 |
3 |
Пустая комната 1 |
2 |
7 |
NULL |
Свободный компьютер B |
2 |
3 |
Пустая комната 1 |
2 |
8 |
NULL |
Свободный компьютер C |
3 |
4 |
Пустая комната 2 |
2 |
6 |
NULL |
Свободный компьютер A |
1 |
4 |
Пустая комната 2 |
2 |
7 |
NULL |
Свободный компьютер B |
2 |
4 |
Пустая комната 2 |
2 |
8 |
NULL |
Свободный компьютер C |
3 |
5 |
Пустая комната 3 |
2 |
6 |
NULL |
Свободный компьютер A |
1 |
5 |
Пустая комната 3 |
2 |
7 |
NULL |
Свободный компьютер B |
2 |
5 |
Пустая комната 3 |
2 |
8 |
NULL |
Свободный компьютер C |
3 |
Условие в строке 24 не допускает в выборку записи (отмечены серым фоном), в которых значение поля position больше значения поля r_space. Таким образом получается финальный результат.
В MS SQL Server всё снова намного проще.
MS SQL І Решение 2.2.10.1
1SELECT [r id],
2[r name]
3[r_space],
4[c id],
5[c room]
6[c name]
7 |
FROM (SELECT [r_id], |
|
|
8 |
|
[r name] |
|
9 |
|
[r_space] |
|
10 |
FROM |
[rooms] |
|
11 |
WHERE |
[r id] NOT IN (SELECT [c room] |
|
12 |
|
FROM |
[computers] |
13 |
|
WHERE |
[c room] IS NOT NULL)) |
14AS [empty rooms]
15CROSS APPLY (SELECT TOP ([r space]) [c id],
16 |
|
[c room] , |
17 |
|
[c name] |
18 |
FROM |
[computers] |
19 |
WHERE |
[c room] IS NULL |
20 |
ORDER BY [c name] ASC) |
|
21 |
AS [cross apply data] |
|
22 |
ORDER BY [r id], |
|
23 |
[c id] |
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 181/545
Пример 20: все разновидности запросов на объединение в трёх СУБД
Подзапрос в строках 7-14 возвращает список пустых комнат:
r_id |
r_name |
r_space |
|
|
|
3 |
Пустая комната 1 |
2 |
|
|
|
4 |
Пустая комната 2 |
2 |
|
|
|
5 |
Пустая комната 3 |
2 |
|
|
|
|
Затем благодаря CROSS |
APPLY в качестве аргумента TOP используется зна- |
|||
чение поля r_space: |
|
|
|
|
|
|
|
|
|
|
|
r id |
r_name |
r_space |
Что подставляется в TOP x |
|
|
3 |
Пустая комната 1 |
2 |
|
SELECT TOP 2 ... |
|
4 |
Пустая комната 2 |
2 |
|
SELECT TOP 2 ... |
|
5 |
Пустая комната 3 |
2 |
|
SELECT TOP 2 ... |
|
Таким образом получается финальный результат.
Oracl |
і |
Решение 2.2.10.l |
I |
|
|
|
e |
|
|
||||
|
|
|
|
|
|
|
1 |
SELECT "r id", |
|
|
|
||
2 |
|
|
"r name" |
|
|
|
3 |
|
|
"r_space", |
|
|
|
4 |
|
|
"c id", |
|
|
|
5 |
|
|
"c room" |
|
|
|
6 |
|
|
"c name" |
|
|
|
7 |
FROM |
(SELECT "r id", |
|
|
||
8 |
|
|
"r name" |
|
|
|
9 |
|
|
"r_space" |
|
|
|
10 |
|
|
FROM |
"rooms" |
|
|
11 |
|
|
WHERE |
"r id" NOT IN (SELECT "c room" |
||
12 |
|
|
|
|
FROM |
"computers" |
13 |
|
|
|
|
WHERE |
"c room" IS NOT NULL)) |
14 |
|
|
"empty rooms" |
|
|
|
15 |
|
|
CROSS JOIN (SELECT "c id", |
|
||
16 |
|
|
|
|
"c room" |
|
17 |
|
|
|
|
"c name" |
|
18 |
|
|
|
|
ROW NUMBER() |
|
19 |
|
|
|
|
OVER ( |
|
20 |
|
|
|
|
ORDER BY "c name" ASC) AS "position" |
|
21 |
|
|
|
FROM |
"computers" |
|
22 |
|
|
|
WHERE |
"c room" IS NULL |
|
23 |
|
|
|
ORDER BY "c name" ASC) |
||
24 |
|
|
"cross apply data" |
|
|
|
25 |
WHERE |
"position" <= "r_space" |
|
|||
26 |
ORDER BY "r id", |
|
|
|||
27 |
|
|
"c id" |
|
|
|
Решение для Oracle эквивалентно решению для MySQL и отличается только способом нумерации компьютеров: здесь мы можем использовать готовую функцию
ROW_NUMBER.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 182/545
Пример 20: все разновидности запросов на объединение в трёх СУБД
Задача 2.2.10. m: показать возможные варианты расстановки свободных компьютеров по комнатам (учитывать остаточную вместимость комнат).
Ожидаемый результат 2.2.10.m.
r_id |
r_name |
r_space |
r_space_left |
c_id |
c_name |
1 |
Комната с двумя компьютерами |
5 |
3 |
6 |
Свободный компьютер A |
1 |
Комната с двумя компьютерами |
5 |
3 |
7 |
Свободный компьютер B |
1 |
Комната с двумя компьютерами |
5 |
3 |
8 |
Свободный компьютер C |
2 |
Комната с тремя компьютерами |
5 |
2 |
6 |
Свободный компьютер A |
2 |
Комната с тремя компьютерами |
5 |
2 |
7 |
Свободный компьютер B |
3 |
Пустая комната 1 |
2 |
2 |
6 |
Свободный компьютер A |
3 |
Пустая комната 1 |
2 |
2 |
7 |
Свободный компьютер B |
4 |
Пустая комната 2 |
2 |
2 |
6 |
Свободный компьютер A |
4 |
Пустая комната 2 |
2 |
2 |
7 |
Свободный компьютер B |
5 |
Пустая комната 3 |
2 |
2 |
6 |
Свободный компьютер A |
5 |
Пустая комната 3 |
2 |
2 |
7 |
Свободный компьютер B |
Решение 2.2.10.m: используем CROSS APPLY в MS SQL Server и эмуляцию аналогичного поведения в MySQL и Oracle.
Данная задача похожа на задачу 2.2.10.j{166} за тем исключением, что здесь мы учитываем не общую вместимость комнаты, а остаточную — т.е. разницу между вместимостью комнаты и количеством уже расположенных в ней компьютеров.
MyS QL і Решение 2.2.10.m і
1
2
3 |
|
|
|
|
4 |
SELECT 'r_id', |
|
|
|
5 |
'r_name', 'r_space', ( |
|
'r_space' - |
|
6 |
IFNULL('r_used', |
|
|
0) ) AS 'r_space_left', |
7 |
'c_id', 'c_name' 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') AS 'computers_in_room' |
||
12 |
ON 'r_id' = 'c_room_inner' |
|||
13 |
CROSS JOIN (SELECT 'c_id', |
|
||
14 |
|
'c_room', 'c_name', @row_num := |
||
15 |
|
@row_num + 1 AS 'position' |
||
16 |
FROM |
'computers', |
||
17 |
|
(SELECT @row_num := 0) AS 'x' WHERE |
||
18 |
|
'c_room' IS NULL |
||
19 |
ORDER BY |
'c_name' ASC) AS |
||
20 |
|
|
'cross_apply_data' |
|
21WHERE 'position' <= ('r_space' - IFNULL('r_used', 0))
22ORDER BY 'r_id',
23'c id'
Подзапрос в строках 13-20 возвращает пронумерованный список свободных компьютеров:
c_id |
c_room |
c_name |
position |
6 |
NULL |
Свободный компьютер A |
1 |
7 |
NULL |
Свободный компьютер B |
2 |
8 |
NULL |
Свободный компьютер C |
3 |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 183/545
Пример 20: все разновидности запросов на объединение в трёх СУБД
Подзапрос в строках 8-11 возвращает информацию о количестве компьютеров в каждой комнате:
c_room_inner |
r_used |
NULL |
0 |
1 |
2 |
2 |
3 |
После выполнения LEFT JOIN в строке 8 информация о количестве компьютеров в комнате объединяется со списком комнат:
r id |
r_name |
r_space |
r_used |
1 |
Комната с двумя компьютерами |
5 |
2 |
2 |
Комната с тремя компьютерами |
5 |
3 |
3 |
Пустая комната 1 |
2 |
NULL |
4 |
Пустая комната 2 |
2 |
NULL |
5 |
Пустая комната 3 |
2 |
NULL |
В строках 4 и 21 информация о количестве компьютеров в комнате используется для вычисления оставшегося количества свободных мест (так получается значение поля r_space_left). Обратите внимание на необходимость использова-
ния функции IFNULL для преобразования к 0 значения NULL поля r_used у пустых
комнат.
Условие в строке 21 не допускает попадание в выборку свободных компьютеров с порядковым номером большим, чем количество оставшихся в комнате свободных мест. Так получается финальный результат.
В MS SQL Server решение с использованием CROSS APPLY оказывается более простым и компактным.
MS SQL I Решение 2.2.10.m |
1 |
SELECT [r_id], |
|
2 |
|
[r_name], |
3 |
|
[r_space], |
4 |
|
[r_space_left], |
|
|
[c_id] , |
6 |
|
[c_name] |
|
FROM |
[rooms] |
8 |
|
CROSS APPLY (SELECT TOP ([r_space] - (SELECT COUNT [c_room] FROM |
9 |
|
[computers] WHERE [c_room] = [r_id])) |
10 |
|
[c_id], |
|
11 |
|
( [r_space] - (SELECT COUNT([c_room]) |
|
12 |
|
FROM |
[computers] |
13 |
|
WHERE |
[c_room] = [r_id] ) |
14 |
|
AS [r_space_left], |
|
15 |
|
[c_room] |
|
16 |
|
[c_name] |
|
17 |
FROM |
[computers] |
|
18 |
WHERE |
[c_room] IS NULL |
|
19 |
ORDER BY [c_name] ASC) AS [cross_apply_data] |
||
20 |
ORDER BY [r_id], |
|
|
21 |
[c _id] |
|
|
Благодаря доступу к полям анализируемой записи из левой таблицы подзапросы в строках 8-9 и 11-14 могут сразу получить всю необходимую информацию, и в результате подзапрос в строках 8-19 возвращает для каждой записи из результата левой части CROSS APPLY не большее количество свободных компьютеров, чем в соответствующей комнате осталось свободных мест:
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 184/545