Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 16: запросы на объединение и функция COUNT
Далее выполняется подзапрос, определяющий находящееся на руках у читателей количество экземпляров каждой книги (строки 5-14 исходного запроса):
|
MySQL I Решение 2.2.6.b (фрагмент кода с подзапросом) | |
|||||
|
|
-- Вариант 3: пошаговое применение нескольких подзапросов |
||||
|
5 |
|
• •• |
|
|
|
|
|
|
|
|
|
|
|
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 |
'bid') AS 'real taken' |
|||
|
|
~ - |
|
|
|
|
|
|
|
Результат выполнения этого подзапроса таков: |
|||
|
|
|
|
|
|
|
|
b_id |
|
taken |
|
|
|
|
1 |
|
2 |
|
|
|
|
2 |
|
0 |
|
|
|
|
3 |
|
1 |
|
|
|
|
4 |
|
1 |
|
|
|
|
5 |
|
1 |
|
|
|
|
6 |
|
0 |
|
|
|
|
7 |
|
0 |
|
|
|
Полученные данные используются в коррелирующем подзапросе (строки 416 исходного запроса) для определения итогового результата (количества экземпляров книг в библиотеке):
MySQL |
Решение 2.2.6.b (фрагмент кода с подзапросом) | |
|
|
||
-- Вариант 3 : пошаговое применение нескольких подзапросов |
|||||
4 |
• •• |
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 'bid') 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 в примерах © Богдан Марчук Стр: 115/545
Пример 16: запросы на объединение и функция COUNT
MySQL Решение 2.2.6.b (фрагмент кода с подзапросом)
--Вариант 4: подзапрос используется как эмуляция
--общего табличного выражения
7SELECT 'sb_book',
8COUNT('sb_book') AS 'taken'
9 |
FROM |
'subscriptions' |
|
10 |
WHERE |
'sb_is_active' |
='Y' |
11 |
GROUP BY 'sb_book' |
|
|
Результат выполнения этого подзапроса таков:
sb_book |
taken |
1 |
2 |
3 |
1 |
4 |
1 |
5 |
1 |
Этот результат используется в операторе объединения JOIN (строки 7-12 оригинального запроса), а функция IFNULL в 5-й строке оригинального запроса поз-
воляет получить числовое представление количества выданных на руки читателям книг в случае, если ни один экземпляр не выдан. Для наглядной демонстрации перепишем 4-й вариант, добавив в выборку поля b_quantity и исходное значение taken, полученное в результате объединения с данными подзапроса:
MySQL I |
Решение 2.2.6.b (модифицированный запрос) |
| |
1-- Вариант 4: подзапрос используется как эмуляция
2-- общего табличного выражения
3SELECT 'b id',
4'b name',
5'b quantity',
6'taken',
|
|
( '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 |
Язык программирования C++ |
3 |
1 |
2 |
1 |
Евгений Онегин |
2 |
2 |
0 |
4 |
Психология программирования |
1 |
1 |
0 |
В оригинальном запросе NULL-значения поля taken преобразуются в 0, затем из значения поля b_quantity вычитается значение поля taken и таким образом получаются конечные значения поля real_taken.
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 116/545
Пример 16: запросы на объединение и функция COUNT
Рассмотрим решение задачи 2.2.6.b для MS SQL Server и Oracle. Вариант 1 этих решений полностью идентичен варианту 1 решения для MySQL, а варианты 24 полностью идентичны для MS SQL Server и Oracle, потому мы рассмотрим их только один раз — на примере MS SQL Server.
MS SQL 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 |
|
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] ) ) |
18 |
|
[real count] |
|
19 |
FROM |
[books] |
|
20 |
ORDER BY [real count] DESC |
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 117/545
Пример 16: запросы на объединение и функция COUNT
MS SQL |
Решение 2.2.6.b |
(продолжение) I |
|
1 |
-- Вариант 4: без подзапросов |
||
2 |
WITH [books taken] |
||
3 |
|
AS (SELECT [sb book], |
|
4 |
|
|
COUNT([sb book]) AS [taken] |
5 |
|
FROM |
[ subscriptions] |
6 |
|
WHERE |
[sb is active] = 'Y' |
7 |
|
GROuP |
BY [sb book] |
8 |
SELECT [b id], |
|
|
9 |
|
[b_name], |
|
10 |
|
( [b quantity] - ISNULL([taken] 0 ) AS [real count] |
|
11 |
FROM |
[books] |
|
12 |
|
LEFT OUTER JOIN [books taken] |
|
13 |
|
|
ON [b id] = [sb book] |
14 |
ORDER BY [real |
count] DESC |
|
|
|
|
|
Вариант 2 основан на том, что общее табличное выражение в строках 3-8 подготавливает информацию о количестве экземпляров книг, находящихся на руках у читателей. Выполним отдельно соответствующий фрагмент запроса:
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]) |
|
|
9 |
SELECT * FROM [books_ taken] |
|
|
|||
|
|
|
|
|||
|
|
Результат выполнения этого фрагмента запроса таков: |
||||
|
|
|
|
|
|
|
b_id |
|
taken |
|
|
|
|
1 |
|
2 |
|
|
|
|
3 |
|
1 |
|
|
|
|
4 |
|
1 |
|
|
|
|
5 |
|
1 |
|
|
|
|
|
|
Далее в строках 11-16 исходного запроса выполняется коррелирующий под- |
||||
запрос, возвращающий для каждой книги количество выданных на руки читателям экземпляров или NULL, если ни один экземпляр не выдан. Чтобы иметь возможность корректно использовать такой результат в арифметическом выражении, в строке 11 исходного запроса мы используем функцию ISNULL, преобразующую NULL- значения в 0.
Вариант 3, основанный на пошаговом применении двух общих табличных выражений, подготавливает для коррелирующего подзапроса полностью готовый набор данных.
Первое общее табличное выражение (строки 2-5) возвращает следующие данные:
sb_book
_3 ______
_5 ______
_1 ______
_1 ______
4
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 118/545
Пример 16: запросы на объединение и функция COUNT
Второе общее табличное выражение (строки 6-12) возвращает следующие данные:
b_id |
taken |
1 |
2 |
2 |
0 |
3 |
1 |
4 |
1 |
5 |
1 |
6 |
0 |
7 |
0 |
На основе полученных данных коррелирующий подзапрос в строках 15-18 вычисляет реальное количество экземпляров книг в библиотеке. Поскольку из второго общего табличного выражения данные поступают с «готовыми нулями» для книг, ни один экземпляр которых не выдан читателям, здесь нет необходимости использовать функцию ISNULL.
Вариант 4 основан на предварительной подготовке в общем табличном выражении информации о том, сколько книг выдано читателям, с последующим вычитанием этого количества из количества зарегистрированных в библиотеке книг. Общее табличное выражение возвращает следующие данные:
sb_book |
taken |
1 |
2 |
3 |
1 |
4 |
1 |
5 |
1 |
Поскольку при группировке для книг, ни один экземпляр которых не выдан читателям, значение taken будет равно NULL, мы применяем в 10-й строке запроса функцию ISNULL, преобразующую значения NULL в 0.
Рассмотрим решение задачи 2.2.6.b для Oracle. Единственное заметное отличие этого решения от решения для MS SQL Server заключается в том, что в Oracle используется функция NVL для получения поведения, аналогичного функции
ISNULL в MS SQL Server (подстановка значения 0 вместо NULL).
Oracl |
і |
Решение 2.2.6.b |
I |
|
|
e |
|
|
|||
|
|
|
|
|
|
1 |
-- Вариант 1: использование коррелирующего подзапроса |
|
|||
2 |
SELECT DISTINCT "b id", |
|
|
||
3 |
|
|
"b name", |
|
|
4 |
|
|
( "b quantity" - (SELECT COUNT("int" "sb book" |
|
|
5 |
|
|
FROM |
"subscriptions" "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" "ext" |
|
||
12 |
|
|
ON "books" "b id" = "ext" "sb book" |
|
|
13 |
ORDER BY "real count" DESC |
|
|
||
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 119/545