Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 12: запросы на объединение и преобразование столбцов в строки
Чтобы было проще пояснять, перепишем эту часть запроса в упрощённом
виде:
STUFF ((SELECT {данные из коррелирующего подзапроса} |
|
FOR XML PATH(''), TYPE) value('.', 'nvarchar(max)' ), |
1, 2 '') |
Часть FOR XML PATH (’’), TYPE относится к SELECT и говорит СУБД представить результат выборки как часть XML-документа для пути '' (пустой строки), т.е. — просто в виде обычной строки, хоть пока и выглядящей для СУБД как «некие данные в формате XML».
Промежуточный результат уже выглядит проще:
STUFF (({XML-строка}).value('.', 'nvarchar(max)'), 1, 2, '') |
| |
|||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
Метод value (путь, тип_данных) применяется в MS SQL Server для извлечения строкового представления из XML. Не вдаваясь в подробности, скажем, что первый параметр путь, равный . (точка) говорит, что нужно взять текущий элемент XML-документа (у нас «текущим элементом» является наша строка), а параметр тип_данных выставлен в nvarchar (max) как один из самых удобных в MS SQL Server типов для хранения длинных строк.
Результат уже почти готов:
STUFF ({строка с ", " в начале}, 1, 2, '')
Остаётся только избавиться от символов «, » в начале каждого списка авторов. Это и делает функция STUFF, заменяя в строке «{строка с ", " в
начале}»
символы с первого по второй (см. второй и третий параметры функции, равные 1 и
2соответственно) на пустую строку (см. четвёртый параметр функции, равный, '').
Ина этом с MS SQL Server — всё, переходим к Oracle. Здесь реализуется уже третий вариант решения:
Oracle I Решение 2.2.2.a
1SELECT "b name" AS "book",
2UTL RAW.CAST TO NVARCHAR2
3(
4 |
LISTAGG |
5 |
( |
6 |
UTL_RAW.CAS T_TO_RAW("a_name" , |
7 |
UTL RAW.CAST TO RAW(N', ') |
8 |
) |
9 |
WITHIN GROUP (ORDER BY "a name" |
10)
11AS "author(s)"
12 FROM "books"
13JOIN "m2m books authors" USING ("b id"
14JOIN "authors" USING("a id"
15GROUP BY "b id",
16 |
"b name" |
ВOracle решение получается проще, чем в MS SQL Server, т.к. здесь (начиная
сверсии 11 gR2 поддерживается функция LISTAGG, выполняющая практически то
же самое, что и GROUP_CONCAT в MySQL. Таким образом, строки 4-8 запроса отвечают за «разворачивание в строку части столбца».
Вызовы метода UTL_RAW.CAST_TO_RAW в строках 6-7 и метода UTL_RAW.CAST_TO_NVARCHAR2 в строке 2 нужны для того, чтобы сначала представить текстовые данные в формате, который обрабатывается без
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 80/545
Пример 12: запросы на объединение и преобразование столбцов в строки
интерпретации значений байтов, а затем вернуть обработанные данные из этого формата в тек
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 81/545
Пример 12: запросы на объединение и преобразование столбцов в строки
стовый вид. Без этого преобразования информация об авторах превращается в нечитаемый набор спецсимволов.
В остальном поведение Oracle при решении этой задачи вполне эквивалентно поведению MySQL.
'IT Решение 2.2.2 .b{71}.
Решение 2.2.2.b
1SELECT 'b name'
2AS 'book',
3GROUP CONCAT(DISTINCT 'a name' ORDER BY 'a name' SEPARATOR ', ')
4AS 'author(s)',
5GROUP CONCAT(DISTINCT 'g name' ORDER BY 'g name' SEPARATOR ', ')
6AS 'genre(s)'
7 FROM 'books'
8JOIN 'm2m books authors' USING('b id')
9JOIN 'authors' USING('a id')
10JOIN 'm2m books genres' USING('b id')
11JOIN 'genres' USING('g id')
12GROUP BY 'b id'
13ORDER BY 'b name'
Решение 2.2.2.b для MySQL лишь чуть-чуть сложнее решения 2.2.2.а: здесь появился ещё один вызов GROUP_CONCAT (строки 5-6), и в обоих вызовах GROUP_CONCAT появилось ключевое слово DISTINCT, чтобы избежать дублирова-
ния информации об авторах и жанрах, которое появляется объективным образом в процессе выполнения объединения:
b_name |
a_name |
g name |
|
Евгений Онегин |
А.С. Пушкин |
Классика |
|
А.С. Пушкин |
Поэзия |
||
|
|||
Искусство программирования |
Д. Кнут |
Классика |
|
Д. Кнут |
Программирование |
||
|
|||
Курс теоретической физики |
Е.М. Лифшиц |
Классика |
|
Л.Д. Ландау |
Классика |
||
|
|||
Основание и империя |
А. Азимов |
Фантастика |
|
|
Б. Страуструп |
Программирование |
|
Психология программирования |
Б. Страуструп |
Психология |
|
Д. Карнеги |
Программирование |
||
|
|||
|
Д. Карнеги |
Психология |
|
Сказка о рыбаке и рыбке |
А.С. Пушкин |
Классика |
|
А.С. Пушкин |
Поэзия |
||
|
|||
Язык программирования C++ |
Б. Страуструп |
Программирование |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 82/545
Пример 12: запросы на объединение и преобразование столбцов в строки
MS SQL I Решение 2.2.2.b |
| |
|
|
|
||
1 |
WITH [prepared data] |
|
|
|
||
2 |
AS (SELECT [books] [b id] |
|
||||
3 |
|
|
[b_name], |
|
|
|
4 |
|
|
[a name], |
|
|
|
5 |
|
|
|
[g name] |
|
|
6 |
|
FROM |
[books] |
|
|
|
7 |
|
|
JOIN [m2m books authors] |
|
||
8 |
|
|
ON [books] [b id] = [m2m books authors [b id] |
|||
9 |
|
|
JOIN [authors] |
|
||
10 |
|
|
ON [m2m books authors] [a id] |
[authors] [a id] |
||
11 |
|
|
JOIN [m2m books genres] |
|
||
12 |
|
|
ON [books] [b id] = [m2m books genres] [b_id] |
|||
13 |
|
|
JOIN [genres] |
|
||
14 |
|
|
ON [m2m books genres] [g id] = [genres [g id] |
|||
15 |
|
) |
|
|
|
|
16 |
SELECT [outer] [b name] |
|
|
|||
17 |
|
AS [book], |
|
|
|
|
18 |
|
STUFF ((SELECT DISTINCT ', ' + [inner] [a name] |
||||
19 |
|
|
FROM |
[prepared data] AS [inner] |
|
|
20 |
|
|
WHERE |
[outer] [b id] = [inner] |
_id] |
|
21 |
|
|
ORDER BY ', |
' + [inner] [a name] |
|
|
22 |
|
|
FOR XML PATH(''), TYPE) |
'nvarchar(max)'), |
||
23 |
|
|
1, 2 '') |
|
|
|
24 |
|
AS [author(s)], |
|
|
|
|
25 |
|
STUFF ((SELECT DISTINCT ', ' + [inner] [g name] |
||||
26 |
|
|
FROM |
[prepared data] AS [inner] |
|
|
27 |
|
|
WHERE |
[outer] [b id] = [inner] |
_id] |
|
28 |
|
|
ORDER BY ', |
' + [inner] [g name] |
|
|
29 |
|
|
FOR XML PATH(''), TYPE) |
'nvarchar(max)'), |
||
30 |
|
|
1, 2 '') |
|
|
|
31 |
|
AS [genre(s)] |
|
|
|
|
32 |
FROM |
[prepared data] AS [outer] |
|
|||
33 |
GROUP |
BY [outer] [b id], |
|
|
||
34 |
|
[outer] [b name] |
|
|
||
|
|
|
|
|
|
|
Данное решение для MS SQL Server тоже строится на основе решения 2.2.2. a{72} и отличается чуть большим количеством JOIN в общем табличном выражении (добавились строки 11-14), а также (по тем же причинам, что и в MySQL) добавлением DISTINCT в строках 18 и 25. Блоки строк 18-24 и 25-31 отличаются только именем выбираемого поля (в первом случае — a_name, во втором — g_name).
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 83/545
Пример 12: запросы на объединение и преобразование столбцов в строки
Oracle I Решение 2.2.2.b |
|
|
1 |
SELECT "book", "author(s)" |
|
2 |
UTL RAW.CAST TO NVARCHAR2 |
|
3 |
( |
|
4 |
LISTAGG |
|
5 |
( |
|
6 |
|
UTL RAW.CAST TO RAW("g name" , |
7 |
|
UTL RAW.CAST TO RAW(N', ') |
8 |
) |
|
9 |
WITHIN GROUP (ORDER BY "g name" |
|
10 |
) |
|
11 |
AS "genre(s)" |
|
12 |
FROM |
|
13 |
( |
|
14 |
SELECT "b id", "b name" AS "book", |
|
15 |
|
UTL RAW.CAST TO NVARCHAR2 |
16 |
|
( |
17 |
|
LISTAGG |
18 |
|
( |
19 |
|
UTL_RAW.CAST_TO_RAW "a_name"|, |
20 |
|
UTL RAW.CAST TO RAW(N', ') |
21 |
|
) |
22 |
|
WITHIN GROUP (ORDER BY "a name") |
23 |
|
) |
24 |
|
AS "author(s)" |
25 |
FROM |
"books" |
26 |
JOIN "m2m books authors" USING ("b id" |
|
27 |
JOIN "authors" USING("a id" |
|
28 |
GROUP |
BY "b id" |
29 |
|
"b name" |
30 |
) "first level" |
|
31 |
JOIN "m2m books genres" USING ("b id" |
|
32 |
JOIN "genres" USING("g id" |
|
33 |
GROUP BY "b id", |
|
34 |
"book", |
|
35 |
"author(s)" |
|
|
|
|
Здесь подзапрос в строках 13-30 представляет собой решение 2.2.2.a{72}, в котором в SELECT добавлено поле b_id, чтобы оно было доступно для дальнейших операций JOIN и GROUP BY. Если переписать запрос с учётом этой информации, получается:
Oracle I Решение 2.2.2.b
1SELECT "book", "author(s)"
2UTL RAW.CAST TO NVARCHAR2
3(
4 |
LISTAGG |
5 |
( |
6 |
UTL_RAW.CAS T_TO_RAW("g_name" , |
7 |
UTL RAW.CAST TO RAW(N', ') |
8 |
) |
9 |
WITHIN GROUP (ORDER BY "g name" |
10)
11AS "genre(s)"
12FROM {данные из решения 222a + поле b id}
13JOIN "m2m books genres" USING ("b id"
14JOIN "genres" USING("g id"
15GROUP BY "b id",
16 |
"book", |
17 |
"author(s)" |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 84/545