Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 16: запросы на объединение и функция COUNT
Далее из значения поля b_count для каждой книги вычитается результат выполнения коррелирующего подзапроса (строки 4-7 в исходном запросе):
|
MySQL |
Решение 2.2.6.b (фрагмент кода с подзапросом) |
|||
|
|
|
-- Вариант 1: использование коррелирующего подзапроса |
||
|
|
|
-- ... |
|
|
|
4 |
|
SELECT |
COUNT(`int`.`sb_book`) |
|
|
5 |
|
FROM |
`subscriptions` AS `int` |
|
|
6 |
|
WHERE |
`int`.`sb_book` = `ext`.`sb_book` |
|
7AND `int`.`sb_is_active` = 'Y'
--...
Если в таком запросе подставить вместо выражения `ext`.`sb_book` значение идентификатора, то этот фрагмент кода можно будет выполнить как самостоятельный запрос и получить конкретное число (например, для книги с идентификатором 1 это будет число 2, т.е. два экземпляра книги в настоящий момент находится на руках у читателей.
И, наконец, если мы чуть-чуть доработаем исходный запрос так, чтобы видеть все данные по каждой книге (её количество в библиотеке, количество выданных читателям экземпляров, количество оставшихся в библиотеке экземпляров), мы получим следующий неоптимальный, но наглядный запрос (подзапросы в строках 5-8 и 10-13 приходится дублировать, т.к. MySQL не может сразу вычислить количество выданных читателям книг и тут же использовать это значение в выражении, вычисляющем остаток книг в библиотеке):
MySQL Решение 2.2.6.b (переработанный для наглядности запрос)
1-- Вариант 1: использование коррелирующего подзапроса
2SELECT DISTINCT `b_id`,
3 |
|
|
`b_name`, |
|
|
4 |
|
|
`b_quantity`, |
|
|
5 |
|
|
(SELECT |
COUNT(`int`.`sb_book`) |
|
6 |
|
|
FROM |
`subscriptions` AS `int` |
|
7 |
|
|
WHERE |
`int`.`sb_book` = `ext`.`sb_book` |
|
8 |
|
|
|
AND `int`.`sb_is_active` = 'Y') |
|
9 |
|
|
AS `taken`, |
|
|
10 |
|
|
( `b_quantity` - (SELECT |
COUNT(`int`.`sb_book`) |
|
11 |
|
|
|
FROM |
`subscriptions` AS `int` |
12 |
|
|
|
WHERE |
`int`.`sb_book` = `ext`.`sb_book` |
13 |
|
|
|
|
AND `int`.`sb_is_active` = 'Y') ) |
14 |
|
|
AS `real_count` |
|
|
15 |
|
FROM |
`books` |
|
|
16 |
|
|
LEFT OUTER JOIN `subscriptions` AS `ext` |
||
17 |
|
|
ON `books`.`b_id` = `ext`.`sb_book` |
||
18ORDER BY `real_count` DESC
Врезультате выполнения такого запроса получается:
b_id |
b_name |
b_quantity |
taken |
real_count |
6 |
Курс теоретической физики |
12 |
0 |
12 |
7 |
Искусство программирования |
7 |
0 |
7 |
3 |
Основание и империя |
5 |
1 |
4 |
2 |
Сказка о рыбаке и рыбке |
3 |
0 |
3 |
5 |
Язык программирования С++ |
3 |
1 |
2 |
1 |
Евгений Онегин |
2 |
2 |
0 |
4 |
Психология программирования |
1 |
1 |
0 |
Вариант 2 построен на идее эмуляции общих табличных выражений, которые не поддерживаются MySQL. Как показывают дальнейшие исследования скорости выполнения запросов, это окажется очень плохой идеей, но в качестве эксперимента рассмотрим логику работы такого запроса.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 105/545
Пример 16: запросы на объединение и функция COUNT
Работа начинается с самого глубоко вложенного подзапроса (строки 6-10 в исходном запросе):
MySQL Решение 2.2.6.b (фрагмент кода с подзапросом)
|
|
-- Вариант 2: использование подзапроса как эмуляции общего табличного |
||
|
|
-- выражения и коррелирующего подзапроса |
||
|
|
-- ... |
|
|
6 |
|
SELECT |
`sb_book` |
AS `b_id`, |
7 |
|
|
COUNT(`sb_book`) AS `taken` |
|
8 |
|
FROM |
`subscriptions` |
|
9WHERE `sb_is_active` = 'Y'
10GROUP BY `sb_book` -- ...
Результат выполнения данного подзапроса таков (по книгам, ни один экземпляр которых сейчас не находится на руках у читателей, данных здесь нет):
b_id |
taken |
1 |
2 |
3 |
1 |
4 |
1 |
5 |
1 |
Сполученными данными работает коррелирующий подзапрос (строки 5-15),
вкотором функция IFNULL позволяет вместо NULL вернуть значение 0 для книг, ни
один экземпляр которых не был выдан читателям. Для книг, хотя бы один экземпляр которых выдан читателям, возвращается готовое (отличное от нуля) значение. Полученное из подзапроса значение вычитается из значения поля b_quantity, и таким образом мы получаем финальный результат.
Вариант 3 построен на идее последовательного выполнения нескольких подзапросов. Первым срабатывает наиболее глубоко вложенный подзапрос, возвращающий идентификаторы книг, находящихся на руках у читателей (строки 9-12 исходного запроса):
|
MySQL |
|
Решение 2.2.6.b (фрагмент кода с подзапросом) |
|||
|
|
|
-- Вариант 3: пошаговое применение нескольких подзапросов |
|||
|
|
|
-- ... |
|
||
|
9 |
|
(SELECT |
`sb_book` |
||
|
10 |
|
|
FROM |
`subscriptions` |
|
11WHERE `sb_is_active` = 'Y'
12) AS `books_taken` -- ...
Результат выполнения этого подзапроса таков:
sb_book
3
5
1
1
4
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 106/545
Пример 16: запросы на объединение и функция COUNT
Далее выполняется подзапрос, определяющий находящееся на руках у читателей количество экземпляров каждой книги (строки 5-14 исходного запроса):
MySQL Решение 2.2.6.b (фрагмент кода с подзапросом)
|
|
-- Вариант 3: пошаговое применение нескольких подзапросов |
||||
|
|
-- ... |
|
|
|
|
|
5 |
SELECT |
`b_id`, |
|
||
|
6 |
|
|
|
count(`sb_book`) AS `taken` |
|
|
7 |
FROM |
`books` |
|
||
|
8 |
LEFT OUTER JOIN |
|
|
||
|
9 |
|
|
|
( |
|
|
10 |
|
|
|
SELECT |
`sb_book` |
|
11 |
|
|
|
FROM |
`subscriptions` |
|
12 |
|
|
|
WHERE |
`sb_is_active` = 'Y' ) AS `books_taken` |
|
13 |
ON |
`b_id` = `sb_book` |
|||
|
14 |
GROUP BY |
`b_id`) AS `real_taken` |
|||
|
|
-- ... |
|
|
|
|
|
|
|
Результат выполнения этого подзапроса таков: |
|||
|
|
|
|
|
|
|
|
b_id |
|
taken |
|
|
|
|
1 |
|
2 |
|
|
|
|
2 |
|
0 |
|
|
|
|
3 |
|
1 |
|
|
|
|
4 |
|
1 |
|
|
|
|
5 |
|
1 |
|
|
|
|
6 |
|
0 |
|
|
|
|
7 |
|
0 |
|
|
|
Полученные данные используются в коррелирующем подзапросе (строки 4- 16 исходного запроса) для определения итогового результата (количества экземпляров книг в библиотеке):
MySQL Решение 2.2.6.b (фрагмент кода с подзапросом)
|
|
-- Вариант 3: пошаговое применение нескольких подзапросов |
|||
|
|
-- ... |
|
|
|
4 |
|
( `b_quantity` - (SELECT |
`taken` |
|
|
5 |
|
FROM |
(SELECT |
`b_id`, |
|
6 |
|
|
|
COUNT(`sb_book`) AS `taken` |
|
7 |
|
|
FROM |
`books` |
|
8 |
|
|
|
LEFT OUTER JOIN |
|
9 |
|
|
|
(SELECT |
`sb_book` |
10 |
|
|
|
FROM |
`subscriptions` |
11 |
|
|
|
WHERE `sb_is_active` = 'Y' |
|
12 |
|
|
|
) AS `books_taken` |
|
13 |
|
|
|
ON `b_id` = `sb_book` |
|
14 |
|
|
GROUP |
BY `b_id`) AS `real_taken` |
|
15 |
|
WHERE |
`books`.`b_id` = `real_taken`.`b_id`) |
||
16) AS `real_count`
--...
Выполнение этого подзапроса позволяет получить конечное требуемое значение, которое появляется в результатах основного запроса.
Вариант 4 похож на вариант 2 в том, что здесь тоже реализуется эмуляция неподдерживаемых MySQL общих табличных выражений через подзапрос, но есть и существенное отличие от варианта 2 — здесь нет коррелирующих подзапросов.
Эмулирующий общее табличное выражение подзапрос (строки 7-11 оригинального запроса) подготавливает все необходимые данные о том, какое количество экземпляров каждой книги находится на руках у читателей:
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 107/545
Пример 16: запросы на объединение и функция COUNT
MySQL Решение 2.2.6.b (фрагмент кода с подзапросом)
-- Вариант 4: подзапрос используется как эмуляция -- общего табличного выражения
-- ...
7SELECT `sb_book`,
8COUNT(`sb_book`) AS `taken`
9 FROM `subscriptions`
10WHERE `sb_is_active` = 'Y'
11GROUP BY `sb_book` -- ...
Результат выполнения этого подзапроса таков:
sb_book |
taken |
1 |
2 |
3 |
1 |
4 |
1 |
5 |
1 |
Этот результат используется в операторе объединения JOIN (строки 7-12 оригинального запроса), а функция IFNULL в 5-й строке оригинального запроса позволяет получить числовое представление количества выданных на руки читателям книг в случае, если ни один экземпляр не выдан. Для наглядной демонстрации перепишем 4-й вариант, добавив в выборку поля b_quantity и исходное значение taken, полученное в результате объединения с данными подзапроса:
MySQL Решение 2.2.6.b (модифицированный запрос)
1-- Вариант 4: подзапрос используется как эмуляция
2-- общего табличного выражения
3SELECT `b_id`,
4`b_name`,
5`b_quantity`,
6`taken`,
7( `b_quantity` - IFNULL(`taken`, 0) ) AS `real_count`
8 |
|
FROM `books` |
|
|
9 |
|
LEFT OUTER JOIN (SELECT |
`sb_book`, |
|
10 |
|
|
COUNT(`sb_book`) |
AS `taken` |
11 |
|
FROM |
`subscriptions` |
|
12 |
|
WHERE |
`sb_is_active` = |
'Y' |
13 |
|
GROUP |
BY `sb_book`) AS |
`books_taken` |
14 |
|
ON `b_id` = `sb_book` |
|
|
15ORDER BY `real_count` DESC
Врезультате выполнения такого модифицированного запроса получается:
b_id |
b_name |
b_quantity |
taken |
real_count |
6 |
Курс теоретической физики |
12 |
NULL |
12 |
7 |
Искусство программирования |
7 |
NULL |
7 |
3 |
Основание и империя |
5 |
1 |
4 |
2 |
Сказка о рыбаке и рыбке |
3 |
NULL |
3 |
5 |
Язык программирования С++ |
3 |
1 |
2 |
1 |
Евгений Онегин |
2 |
2 |
0 |
4 |
Психология программирования |
1 |
1 |
0 |
В оригинальном запросе NULL-значения поля taken преобразуются в 0, затем из значения поля b_quantity вычитается значение поля taken и таким образом получаются конечные значения поля real_taken.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 108/545
Пример 16: запросы на объединение и функция COUNT
Рассмотрим решение задачи 2.2.6.b для MS SQL Server и Oracle. Вариант 1 этих решений полностью идентичен варианту 1 решения для MySQL, а варианты 2- 4 полностью идентичны для MS SQL Server и Oracle, потому мы рассмотрим их только один раз — на примере MS SQL Server.
MS SQL Решение 2.2.6.b
1-- Вариант 1: использование коррелирующего подзапроса
2SELECT DISTINCT [b_id],
3 |
|
[b_name], |
|
4 |
|
( [b_quantity] - (SELECT |
COUNT([int].[sb_book]) |
5 |
|
FROM |
[subscriptions] AS [int] |
6 |
|
WHERE |
[int].[sb_book] = [ext].[sb_book] |
7 |
|
|
AND [int].[sb_is_active] = 'Y') ) |
8 |
|
AS |
|
9 |
|
[real_count] |
|
|
|
|
|
10 |
|
FROM |
[books] |
|
11 |
|
|
LEFT OUTER JOIN |
[subscriptions] AS [ext] |
12 |
|
|
ON |
[books].[b_id] = [ext].[sb_book] |
13 |
|
ORDER |
BY [real_count] |
DESC |
Описание логики работы варианта 1 см. выше — этот запрос идентичен во всех трёх СУБД.
MS SQL Решение 2.2.6.b
1-- Вариант 2: использование общего табличного выражения
2-- и коррелирующего подзапроса
3WITH [books_taken]
4 |
|
AS (SELECT |
[sb_book] |
AS |
[b_id], |
5 |
|
|
COUNT([sb_book]) |
AS |
[taken] |
6 |
|
FROM |
[subscriptions] |
|
|
7 |
|
WHERE |
[sb_is_active] = |
'Y' |
|
8 |
|
GROUP |
BY [sb_book]) |
|
|
9SELECT [b_id],
10[b_name],
11( [b_quantity] - ISNULL((SELECT [taken]
12 |
|
|
FROM |
[books_taken] |
13 |
|
|
WHERE |
[books].[b_id] = |
14 |
|
|
|
[books_taken].[b_id]), 0 |
15 |
|
|
) ) AS |
|
16 |
|
|
[real_count] |
|
17 |
|
FROM |
[books] |
|
18 |
|
ORDER |
BY [real_count] DESC |
|
|
|
|
|
|
1-- Вариант 3: пошаговое применение общего табличного выражения и подзапроса
2WITH [books_taken]
3AS (SELECT [sb_book]
4 |
|
FROM |
[subscriptions] |
5 |
|
WHERE |
[sb_is_active] = 'Y'), |
6[real_taken]
7AS (SELECT [b_id],
8 |
|
|
COUNT([sb_book]) AS [taken] |
|
9 |
|
FROM |
[books] |
|
10 |
|
|
LEFT OUTER JOIN |
[books_taken] |
11 |
|
|
ON |
[b_id] = [sb_book] |
12GROUP BY [b_id])
13SELECT [b_id],
14[b_name],
15( [b_quantity] - (SELECT [taken]
16 |
|
|
FROM |
[real_taken] |
17 |
|
|
WHERE |
[books].[b_id] = [real_taken].[b_id]) ) AS |
18 |
|
|
[real_count] |
|
19 |
|
FROM |
[books] |
|
20 |
|
ORDER |
BY [real_count] DESC |
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 109/545