Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 16: запросы на объединение и функция COUNT
2.2.6. Пример 16: запросы на объединение и функция COUNT
Задача 2.2.6.a{102}: показать книги, у которых более одного автора.
Задача 2.2.6.b{103}: показать, сколько реально экземпляров каждой книги сейчас есть в библиотеке.
Ожидаемый результат 2.2.6.a.
b_id |
b_name |
authors_count |
|
4 |
Психология программирования |
2 |
|
6 |
Курс теоретической физики |
2 |
|
|
Ожидаемый результат 2.2.6.b. |
|
|
b_id |
b_name |
real_count |
|
6 |
Курс теоретической физики |
12 |
|
7 |
Искусство программирования |
7 |
|
3 |
Основание и империя |
4 |
|
2 |
Сказка о рыбаке и рыбке |
3 |
|
5 |
Язык программирования C++ |
2 |
|
1 |
Евгений Онегин |
0 |
|
4 |
Психология программирования |
0 |
|
уЦ7
А/1’ Решение 2.2.6.a{102}.
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 110/545
Пример 16: запросы на объединение и функция COUNT
Обратите внимание, насколько решение для MySQL короче и проще решений для MS SQL Server и Oracle за счёт того, что в MySQL нет необходимости указывать имя таблицы для разрешения неоднозначности принадлежности поля b_id, а также нет необходимости группировать результат по полю b_name.
ХЙАЙ'
Решение 2.2.6.b<102\
Мы рассмотрим несколько похожих по сути, но принципиально разных по синтаксису решений, чтобы показать широту возможностей языка SQL и используемых СУБД, а также чтобы сравнить скорость работы различных решений.
В решении для MySQL варианты 2 и 3 представлены только затем, чтобы показать, как с помощью подзапросов можно эмулировать неподдерживаемые MySQL общие табличные выражения.
MySQL I Решение 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 |
WHER |
'int' 'sb book' = 'ext' 'sb book' |
7 |
|
AND 'int' 'sb is active' = 'Y') |
8 |
) AS |
real_count' |
9FROM 'books'
10LEFT OUTER JOIN 'subscriptions' AS 'ext'
11 |
ON 'books' 'b id' 'ext' 'sb book' |
12 |
ORDER BY 'real_count' DESC |
1-- Вариант 2: использование подзапроса как эмуляции общего табличного
2-- выражения и коррелирующего подзапроса
3SELECT 'b id' ,
4'b name',
5( 'b quantity' - IFNULL((SELECT 'taken'
6 |
FROM |
(SELECT 'sb book' |
AS 'b id', |
|
7 |
|
|
COUNT('sb book') AS 'taken' |
|
8 |
|
FROM |
'subscriptions' |
|
9 |
|
WHERE |
'sb is active' = 'Y' |
|
10 |
|
GROUP |
BY 'sb book' |
|
11 |
|
) AS 'books taken' |
|
|
12 |
WHERE |
'books' 'b id' = |
|
|
13 |
|
'books taken' 'b id'), 0 |
|
|
14 |
) ) AS |
|
|
|
15'real count'
16FROM 'books'
17ORDER BY 'real count' DESC
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 111/545
Пример 16: запросы на объединение и функция COUNT
MySQL I |
Решение 2.2.6.b (продолжение) |
| |
|
|
|
|||
1 |
-- Вариант 3: пошаговое применение нескольких подзапросов |
|||||||
2 |
SELECT 'b id' , |
|
|
|
|
|
||
3 |
|
|
'b name', |
|
|
|
|
|
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 ' |
|
|
|
|
|
17 |
FROM |
|
'books' |
|
|
|
|
|
18 |
ORDER BY 'real_count' |
DESC |
|
|
|
|
||
1 |
-- Вариант 4: подзапрос используется как эмуляция общего |
|||||||
2 |
-- табличного выражения |
|
|
|
|
|
||
3 |
SELECT 'b id' , |
|
|
|
|
|
||
4 |
|
|
'b name', |
|
|
|
|
|
5 |
|
|
( 'b quantity' |
IFNULL( 'taken', |
0) ) AS 'real count' |
|||
6 |
FROM |
|
'books' |
|
|
|
|
|
7 |
|
|
LEFT OUTER JOIN |
(SELECT 'sb |
|
|
||
8 |
|
|
|
|
|
COUNT('s book') AS 'taken' |
||
9 |
|
|
|
FROM |
|
'subscriptions' |
|
|
10 |
|
|
|
WHERE |
|
' sb is active' |
= 'Y' |
|
11 |
|
|
|
GROUP |
|
BY 'sb book') AS 'books taken' |
||
12 |
|
|
ON |
'b id' |
'sb book' |
|
||
13 |
ORDER |
BY 'real_count' |
DESC |
|
|
|
|
|
Рассмотрим логику работы этих запросов.
Вариант 1 начинается с выполнения объединения. Если временно заменить подзапрос в строках 4-8 на константу, получается следующий код:
MySQL Решение 2.2.6.b (модифицированный запрос)
1-- Вариант 1: использование коррелирующего подзапроса
2SELECT DISTINCT 'b_id',
3 |
|
'b_name', |
4 |
|
'X' AS 'real_count' |
5 |
FROM |
'books' |
6 |
|
LEFT OUTER JOIN 'subscriptions' AS 'ext' |
7 |
|
ON 'books' 'b_id' = 'ext' |
8ORDER BY 'real count' DESC
Врезультате выполнение такого запроса получим:
b_id |
b_name |
real_count |
1 |
Евгений Онегин |
X |
2 |
Сказка о рыбаке и рыбке |
X |
3 |
Основание и империя |
X |
4 |
Психология программирования |
X |
5 |
Язык программирования C++ |
X |
6 |
Курс теоретической физики |
X |
7 |
Искусство программирования |
X |
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 112/545
Пример 16: запросы на объединение и функция COUNT
Далее из значения поля b_count для каждой книги вычитается результат выполнения коррелирующего подзапроса (строки 4-7 в исходном запросе):
MySQL |
Решение 2.2.6.b (фрагмент кода с подзапросом) |
|
|
|
||
|
- |
|
|
|
|
|
- Вариант 1: использование коррелирующего подзапроса |
|
|
||||
4 |
SELECT COUNT('int' 'sb_book') |
|
|
|
||
5 |
FROM |
'subscriptions' AS 'int' 'int'.'sb_book' |
= |
'ext' 'sb_book' |
||
6 |
WHERE |
AND 'int'.'sb is active' = |
'Y' |
|
|
|
7 |
|
|
|
|
|
|
Если в таком запросе подставить вместо выражения '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' |
|
|
|
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 |
Язык программирования C++ |
3 |
1 |
2 |
1 |
Евгений Онегин |
2 |
2 |
0 |
4 |
Психология программирования |
1 |
1 |
0 |
Вариант 2 построен на идее эмуляции общих табличных выражений, которые не поддерживаются MySQL. Как показывают дальнейшие исследования скорости выполнения запросов, это окажется очень плохой идеей, но в качестве эксперимента рассмотрим логику работы такого запроса.
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 113/545
Пример 16: запросы на объединение и функция COUNT
Работа начинается с самого глубоко вложенного подзапроса (строки 6-10 в исходном запросе):
MySQL і Решение 2.2.6.b (фрагмент кода с подзапросом)
--Вариант 2: использование подзапроса как эмуляции общего табличного
--выражения и коррелирующего подзапроса
6 |
|
|
|
|
|
7 |
|
|
|
|
|
8 |
SELECT 'sb book' |
|
b id', |
||
|
F M |
|
crip |
ook'ns |
|
|
GROUP |
BY 'sb_book' |
|
||
|
WHERE |
'subis active' = 'Y' |
|
||
9 |
— — • •• |
|
|
|
|
|
|
|
|
|
|
10 |
|
|
|
|
|
Результат выполнения данного подзапроса таков (по книгам, ни один экземпляр которых сейчас не находится на руках у читателей, данных здесь нет):
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'
10FROM 'subscriptions'
WHERE 'sb_is_active' = 'Y'
12 ) AS 'books_taken'
Результат выполнения этого подзапроса таков:
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 114/545