Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 15: двойное использование условия IN
Решение 2.2.5.c{97}.
Здесь по-прежнему удобно использовать IN для указания условия выборки, но второе использование IN по условию задачи необходимо заменить на JOIN.
|
MySQL |
Решение 2.2.5.c |
||||
|
1 |
|
SELECT |
DISTINCT `b_id`, |
|
|
|
2 |
|
|
|
`b_name` |
|
|
3 |
|
FROM |
`books` |
|
|
|
4 |
|
|
|
JOIN `m2m_books_genres` USING ( `b_id` ) |
|
|
5 |
|
WHERE |
`g_id` IN ( 2, 5 ) |
|
|
|
6 |
|
ORDER |
BY `b_name` ASC |
|
|
|
|
|
||||
|
MS SQL |
Решение 2.2.5.c |
||||
|
1 |
|
SELECT |
DISTINCT [books].[b_id], |
|
|
|
2 |
|
|
|
[b_name] |
|
|
3 |
|
FROM |
[books] |
|
|
|
|
|
|
|
|
|
4JOIN [m2m_books_genres]
5ON [books].[b_id] = [m2m_books_genres].[b_id]
|
6 |
|
WHERE |
[g_id] IN ( |
2, 5 ) |
|
|
|
7 |
|
ORDER |
BY [b_name] |
ASC |
|
|
|
|
|
|
|
|
|
|
|
Oracle |
|
Решение 2.2.5.c |
|
|
||
|
1 |
|
SELECT |
DISTINCT "b_id", |
|
||
|
2 |
|
|
|
"b_name" |
|
|
|
3 |
|
FROM |
"books" |
|
|
|
|
4 |
|
|
|
JOIN "m2m_books_genres" USING ( "b_id" ) |
|
|
|
5 |
|
WHERE |
"g_id" IN ( |
2, 5 ) |
|
|
|
6 |
|
ORDER |
BY "b_name" |
ASC |
|
|
Мы не можем обойтись без ключевого слова DISTINCT, т.к. в противном случае получим дублирование результатов для книг, относящихся одновременно к обоим требуемым жанрам:
b_id |
b_name |
1 |
Евгений Онегин |
7 |
Искусство программирования |
7 |
Искусство программирования |
6 |
Курс теоретической физики |
4 |
Психология программирования |
2 |
Сказка о рыбаке и рыбке |
5 |
Язык программирования С++ |
Но благодаря тому, что мы помещаем в выборку идентификатор книги, мы не рискуем «схлопнуть» с помощью DISTINCT несколько книг с одинаковыми названиями в одну строку.
Обратите внимание на синтаксические различия этого решения для разных СУБД: MySQL и Oracle не требуют указания на то, из какой таблицы выбирать значение поля b_id (строка 1 запросов 2.2.5.c для MySQL и Oracle), а также поддерживают сокращённую форму указания условия объединения (строка 4 запросов 2.2.5.c для MySQL и Oracle). MS SQL Server требует указывать таблицу, из которой будет происходить извлечение значения поля b_id (строка 1 запроса 2.2.5.c для MS SQL Server), а также не поддерживает сокращённую форму указания условия объединения (строка 5 запроса 2.2.5.c для MS SQL Server).
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 100/545
Пример 15: двойное использование условия IN
Решение 2.2.5.d{97}:
Поскольку первое использование IN по условию задачи пришлось заменить на JOIN, здесь осталось на один уровень IN меньше, чем в решении 2.2.5.b{99}.
|
MySQL |
Решение 2.2.5.d |
|
|
|||
|
1 |
|
SELECT |
DISTINCT `b_id`, |
|
|
|
|
2 |
|
|
|
`b_name` |
|
|
|
3 |
|
FROM |
`books` |
|
|
|
|
4 |
|
|
|
JOIN `m2m_books_genres` USING ( `b_id` ) |
|
|
|
5 |
|
WHERE |
`g_id` IN (SELECT |
`g_id` |
|
|
|
6 |
|
|
|
FROM |
`genres` |
|
|
7 |
|
|
|
WHERE |
`g_name` IN ( N'Программирование', |
|
|
8 |
|
|
|
|
N'Классика' ) |
|
|
9 |
|
|
|
) |
|
|
|
10 |
|
ORDER |
BY `b_name` ASC |
|
|
|
|
|
|
|
|
|||
|
MS SQL |
Решение 2.2.5.d |
|
|
|||
|
1 |
|
SELECT |
DISTINCT [books].[b_id], |
|
||
|
2 |
|
|
|
[b_name] |
|
|
|
3 |
|
FROM |
[books] |
|
|
|
4JOIN [m2m_books_genres]
5ON [books].[b_id] = [m2m_books_genres].[b_id]
|
6 |
|
WHERE |
[g_id] IN (SELECT |
[g_id] |
|
|
|
7 |
|
|
|
FROM |
[genres] |
|
|
8 |
|
|
|
WHERE |
[g_name] IN ( N'Программирование', |
|
|
9 |
|
|
|
|
N'Классика' ) |
|
|
10 |
|
|
|
) |
|
|
|
11 |
|
ORDER |
BY [b_name] ASC |
|
|
|
|
|
|
|
|
|
|
|
|
Oracle |
|
Решение 2.2.5.d |
|
|
||
|
1 |
|
SELECT |
DISTINCT "b_id", |
|
|
|
|
2 |
|
|
|
"b_name" |
|
|
|
3 |
|
FROM |
"books" |
|
|
|
|
4 |
|
|
|
JOIN "m2m_books_genres" USING ( "b_id" ) |
|
|
|
5 |
|
WHERE |
"g_id" IN (SELECT |
"g_id" |
|
|
|
6 |
|
|
|
FROM |
"genres" |
|
|
7 |
|
|
|
WHERE |
"g_name" IN ( N'Программирование', |
|
|
8 |
|
|
|
|
N'Классика' ) |
|
|
9 |
|
|
|
) |
|
|
|
10 |
|
ORDER |
BY "b_name" ASC |
|
|
|
Логика решения 2.2.5.d является комбинацией 2.2.5.b (по самым глубоко вложенным IN) и 2.2.5.с (по использованию JOIN).
Задание 2.2.5.TSK.A: показать книги, написанные Пушкиным и/или Азимовым (индивидуально или в соавторстве — не важно).
Задание 2.2.5.TSK.B: показать книги, написанные Карнеги и Страустру-
пом в соавторстве.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 101/545
Пример 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 |
Язык программирования С++ |
2 |
|
1 |
Евгений Онегин |
0 |
|
4 |
Психология программирования |
0 |
|
Решение 2.2.6.a{102}.
MySQL Решение 2.2.6.a
1SELECT `b_id`,
2`b_name`,
3COUNT(`a_id`) AS `authors_count`
4 |
|
FROM |
`books` |
5 |
|
|
JOIN `m2m_books_authors` USING (`b_id`) |
6 |
|
GROUP |
BY `b_id` |
7 |
|
HAVING |
`authors_count` > 1 |
|
|
|
|
MS SQL Решение 2.2.6.a
1SELECT [books].[b_id],
2[books].[b_name],
3COUNT([m2m_books_authors].[a_id]) AS [authors_count]
4 FROM [books]
5JOIN [m2m_books_authors]
6ON [books].[b_id] = [m2m_books_authors].[b_id]
7 |
|
GROUP |
BY [books].[b_id], |
8 |
|
|
[books].[b_name] |
9 |
|
HAVING |
COUNT([m2m_books_authors].[a_id]) > 1 |
Oracle Решение 2.2.6.a
1SELECT "b_id",
2"b_name",
3COUNT("a_id") AS "authors_count"
4 |
|
FROM |
"books" |
5 |
|
|
JOIN "m2m_books_authors" USING ("b_id") |
6 |
|
GROUP |
BY "b_id", "b_name" |
7 |
|
HAVING |
COUNT("a_id") > 1 |
|
|
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 102/545
Пример 16: запросы на объединение и функция COUNT
Обратите внимание, насколько решение для MySQL короче и проще решений для MS SQL Server и Oracle за счёт того, что в MySQL нет необходимости указывать имя таблицы для разрешения неоднозначности принадлежности поля b_id, а также нет необходимости группировать результат по полю b_name.
Решение 2.2.6.b{102}.
Мы рассмотрим несколько похожих по сути, но принципиально разных по синтаксису решений, чтобы показать широту возможностей языка SQL и используемых СУБД, а также чтобы сравнить скорость работы различных решений.
В решении для MySQL варианты 2 и 3 представлены только затем, чтобы показать, как с помощью подзапросов можно эмулировать неподдерживаемые MySQL общие табличные выражения.
MySQL Решение 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 `real_count` |
|
9 |
|
FROM |
`books` |
|
10 |
|
|
LEFT 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` |
|
|
|
16 |
|
FROM |
`books` |
|
|
|
17 |
|
ORDER |
BY `real_count` DESC |
|
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 103/545
Пример 16: запросы на объединение и функция COUNT
MySQL Решение 2.2.6.b (продолжение)
1-- Вариант 3: пошаговое применение нескольких подзапросов
2SELECT `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-- табличного выражения
3SELECT `b_id`,
4`b_name`,
5( `b_quantity` - IFNULL(`taken`, 0) ) AS `real_count`
6 |
|
FROM |
`books` |
|
|
|
7 |
|
|
LEFT OUTER JOIN |
(SELECT |
`sb_book`, |
|
8 |
|
|
|
|
COUNT(`sb_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`.`sb_book` |
8ORDER BY `real_count` DESC
Врезультате выполнение такого запроса получим:
b_id |
b_name |
real_count |
1 |
Евгений Онегин |
X |
2 |
Сказка о рыбаке и рыбке |
X |
3 |
Основание и империя |
X |
4 |
Психология программирования |
X |
5 |
Язык программирования С++ |
X |
6 |
Курс теоретической физики |
X |
7 |
Искусство программирования |
X |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 104/545