Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)

Пример 11: запросы на объединение как способ получения человекочитаемых данных

Oracle Решение 2.2.1.b

1SELECT "b_name",

2"s_id",

3"s_name",

4"sb_start",

5"sb_finish"

6 FROM "books"

7JOIN "subscriptions"

8ON "b_id" = "sb_book"

9JOIN "subscribers"

10ON "sb_subscriber" = "s_id"

Логика решения 2.2.1.b{69} полностью эквивалентна логике решения 2.2.1.a{67} (здесь приходится объединять даже меньше таблиц — всего три, а не пять). Обратите внимание на тот факт, что если объединение происходит не по одноимённым полям, а по разноимённым, в MySQL и Oracle тоже приходится использовать конструкцию ON вместо USING.

Задание 2.2.1.TSK.A: показать список книг, у которых более одного автора.

Задание 2.2.1.TSK.B: показать список книг, относящихся ровно к одному жанру.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 70/545

Пример 12: запросы на объединение и преобразование столбцов в строки

2.2.2.Пример 12: запросы на объединение и преобразование столбцов в строки

Возможно, вы заметили, что в решении{67} задачи 2.2.1.a{66} есть неудобство: если у книги несколько авторов и/или жанров, информация начинает дублироваться (упорядочим для наглядности выборку по полю b_name):

Пользователи же куда больше привыкли к следующему представлению дан-

ных:

Задача 2.2.2.a{72}: показать все книги с их авторами (дублирование названий книг не допускается).

Задача 2.2.2.b{76}: показать все книги с их авторами и жанрами (дублирование названий книг и имён авторов не допускается).

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 71/545

Пример 12: запросы на объединение и преобразование столбцов в строки

Ожидаемый результат 2.2.2.a.

Решение 2.2.2.a{71}.

MySQL Решение 2.2.2.a

1SELECT `b_name`

2AS `book`,

3GROUP_CONCAT(`a_name` ORDER BY `a_name` SEPARATOR ', ')

4AS `author(s)`

5 FROM `books`

6JOIN `m2m_books_authors` USING(`b_id`)

7JOIN `authors` USING(`a_id`)

Решение для MySQL получается очень простым и элегантным потому, что эта СУБД поддерживает функцию GROUP_CONCAT, которая и выполняет всю основную работу. У этой функции очень развитый синтаксис (даже в нашем случае мы используем сортировку и указание разделителя), с которым обязательно стоит ознакомиться в официальной документации.

Обратите особое внимание на строку 8 запроса: ни в коем случае не стоит выполнять группировку по названию книги! Такая ошибка приводит к тому, что СУБД считает одной и той же книгой несколько разных книг с одинаковым названием (что в реальной жизни может встречаться очень часто). Группировка же по значению первичного ключа таблицы гарантирует, что никакие разные записи не будут смешаны в одну группу.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 72/545

Пример 12: запросы на объединение и преобразование столбцов в строки

И ещё одна особенность MySQL заслуживает внимания: в строке 1 мы извлекаем поле b_name, которое не упомянуто в выражении GROUP BY в строке 8 и не является агрегирующей функцией. MS SQL Server и Oracle не позволяют поступать подобным образом и строго требуют, чтобы любое поле из конструкции SELECT, не являющееся агрегирующей функций, было явно упомянуто в выражении GROUP BY.

Итак, что делает GROUP_CONCAT? Фактически, «разворачивает часть столбца в строку» (одновременно упорядочивая авторов по алфавиту и разделяя их набором символов «, »):

MS SQL Server не поддерживает функцию GROUP_CONCAT, а потому решение для него весьма нетривиально:

MS SQL Решение 2.2.2.a

1WITH [prepared_data]

2AS (SELECT [books].[b_id],

10)

11SELECT [outer].[b_name]

12AS [book],

13STUFF ((SELECT ', ' + [inner].[a_name]

21GROUP BY [outer].[b_id],

22[outer].[b_name]

Начнём рассмотрение со строк 1-10. В них представлено т.н. CTE (Common Table Expression, общее табличное выражение). Очень упрощённо общее табличное выражение можно считать отдельным поименованным запросом, к результату выполнения которого можно обращаться как к таблице. Это особенно удобно, когда таких обращений в дальнейшем используется несколько (без общего табличного выражения с использованием классического подзапроса, тело такого подзапроса пришлось бы писать везде, где необходимо к нему обратиться).

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 73/545

Пример 12: запросы на объединение и преобразование столбцов в строки

В нашем случае общее табличное выражение возвращает такие данные:

Это — уже почти готовое решение, останется только «развернуть в строку» части столбца a_name с авторами одной и той же книги. Эту задачу выполнят строки 13-19 запроса.

Основной рабочей частью процесса является код коррелирующего подза-

проса:

SELECT ', ' + [inner].[a_name]

FROM [prepared_data] AS [inner]

WHERE [outer].[b_id] = [inner].[b_id]

ORDER BY [inner].[a_name]

Для каждой строки результата, полученного из общего табличного выражения, выполняется подзапрос, возвращающий данные из этого же результата, относящиеся к рассматриваемой на внешнем уровне строке. Более простой вариант коррелирующего подзапроса мы уже рассматривали{49}, а теперь покажем, как работает СУБД в данном конкретном случае:

Символы «, » (запятая и пробел), стоящие в начале каждого значения в столбце «Какие данные будут собраны» — не опечатка. Мы явно говорим СУБД извлекать именно текст в виде «, » + имя_автора. Чтобы в конечном результате этих символов не было, мы используем функцию STUFF.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 74/545