Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 46: формирование и анализ иерархических структур
Ожидаемый результат 7.1.1.b.
Для вершины с идентификатором 2 (страница «Читателям») запрос должен возвратить следующие данные:
sp_id |
sp_name |
2 |
Читателям |
5 |
Новости |
6 |
Статистика |
12 |
Текущая |
13 |
Архивная |
14 |
Неофициальная |
Ожидаемый результат 7.1.1.С.
Для вершины с идентификатором 13 (страница «Архивная») запрос должен возвратить следующие данные: path
13
6 __
2 __
1
Также допускается вариант:
path
13,6,2,1
Решение 7.1.1.a{475}.
Решение данной задачи для MS SQL Server и Oracle можно очень легко построить на основе рекурсивных общих табличных выражений. В MySQL же общие табличные выражения не поддерживаются, равно как нет и иной возможности сделать «рекурсивный JOIN», потому для этой СУБД решение будет достаточно не-
тривиальным.
Сначала приведём готовый код функции и пример её использования.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 505/545
Пример 46: формирование и анализ иерархических структур
MySQL |
Решение 7.1.1.a (код функции) |
1DELIMITER $$
2CREATE FUNCTION GET_ALL_CHILDREN start_node INT)
3RETURNS TEXT
4BEGIN
5DECLARE result TEXT;
6SELECT GROUP_CONCAT('children_ids' SEPARATOR ',') INTO result
7 |
FROM |
( |
|
|
8 |
|
SELECT 'sp_id', @parent_values := |
||
9 |
|
|
( |
|
10 |
|
|
SELECT GROUP_CONCAT('sp_id' SEPARATOR ',') |
|
11 |
|
|
FROM |
'site_pages' |
12 |
|
|
WHERE FIND_IN_SET('sp_parent', |
|
13 |
|
|
|
@parent_values) > 0 |
14 |
|
|
) AS 'children_ids' |
|
15 |
|
FROM |
'site_pages' |
|
16 |
|
JOIN |
(SELECT @parent_values := start_nodel |
|
17 |
|
|
|
AS 'initialisation' |
18 |
|
WHERE |
'sp_id' IN |
@parent_values |
19) AS 'data';
20RETURN result 1
21END$$
22DELIMITER ;
Проверить работоспособность и корректность представленного решения можно выполнением следующего кода.
MySQL і Решение 7.1.1.a (пример использования функции) і
1-- Проверка работоспособности функции:
2SELECT GET_ALL_CHILDREN'2) AS 'shildren_of_2';
4-- Использование функции:
5SELECT 'sp_id', 'sp_name', GET_ALL_CHILDREN('sp_id') AS 'children'
6FROM 'site pages';
Второй запрос возвратит следующие данные (список всех страниц сайта библиотеки с указанием идентификаторов всех их подстраниц).
sp_id |
sp_name |
children |
1 |
Главная |
2,3,4,10,5,6,7,8,9,11,12,13,14 |
2 |
Читателям |
5,6,12,13,14 |
3 |
Спонсорам |
7,8,11 |
4 |
Рекламодателям |
9 |
5 |
Новости |
NULL |
6 |
Статистика |
12,13,14 |
7 |
Предложения |
NULL |
8 |
Истории успеха |
NULL |
9 |
Акции |
NULL |
10 |
Контакты |
NULL |
11 |
Документы |
NULL |
12 |
Текущая |
NULL |
13 |
Архивная |
NULL |
14 |
Неофициальная |
NULL |
Теперь рассмотрим, как работает это решение.
Очевидно, главной частью представленной функции является запрос в строках 6-19. Перепишем его без функции.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 506/545
Пример 46: формирование и анализ иерархических структур
|
|
Решение 7.1.1.a |
:, |
основана |
|
1 |
|
|
на |
|
|
SELECT GROUP CONCAT( 'children ids' SEPARATOR ',') AS 'children ids' |
|||||
2 |
FROM |
( |
|
|
|
3 |
|
SELECT 'sp_id', @parent_values := |
|||
4 |
|
|
( |
|
|
5 |
|
|
|
SELECT GROUP CONCAT('sp id' SEPARATOR ',') |
|
6 |
|
|
|
FROM |
'site_pages' |
7 |
|
|
|
WHERE FIND_IN_SET('sp_parent', |
|
8 |
|
|
|
|
@parent values > 0 |
9 |
|
|
) AS 'children ids' |
||
10 |
|
FROM |
'site_pages' |
||
11 |
|
JOIN |
(SELECT @parent values := {родительская вершина}) |
||
12 |
|
|
|
|
AS 'initialisation' |
13 |
|
WHERE |
'sp id' IN |
@parent values) |
|
14) AS 'prepared data'
Втаком виде этот запрос вернёт данные, представленные в ожидаемом результате задачи (если значение {родительская_вершина} равно 2).
Рассмотрим решение по частям.
1)Код в строках 11-12 инициализирует значение переменной @parent_values идентификатором вершины, для которой строится список идентификаторов дочерних вершин. В дальнейшем здесь будет храниться список вершин, но в начале работы здесь помещается только одно значение.
2)Код в строках 5-8 формирует набор идентификаторов дочерних элементов вершин, идентификаторы которых перечислены в переменной @parent_values. Полученный результат используется как новое значение переменной @parent_values.
3)Условие в строке 13 ограничивает выборку только теми вершинами, дочерние элементы которых ищутся на текущем шаге.
4)Алгоритм завершает работу, когда значение переменной @parent_values становится равным NULL.
5)Благодаря GROUP_CONCAT в строке 1 весь результат работу представляется
в виде одного списка идентификаторов, в котором они перечислены через запятую. Такое представление позволяет применять функцию FIND_IN_SET
для дальнейшей работы с полученными результатами.
Если выполнить отдельно строки 3-13 данного запроса, то будет получен следующий результат (сам результат представлен на сером фоне, чтобы не путать его с пояснениями).
Шаг |
sp_id |
children_ids |
Новое значение @parent_values |
Начальное состояние, поиск детей вершины 2. |
|
||
1 |
2 |
5,6 |
5,6 |
Теперь надо найти детей вершин 5 и 6 (строка с вершиной 6 «схлопывается» из-за GROUP_CON- CAT, т.е. в такой выборке мы теряем все значения sp_id, кроме самого первого, но в @parent_values благодаря тому же GROUP_CONCAT попадают дети всех sp_id, даже тех, чьи
значения мы «потеряли»).
2 |
5 |
12,13,14 |
12,13,14 |
Теперь надо найти детей вершин 12, 13 и 14 (строки с вершинами 13 и 14 «схлопываются» из-за GROUP_CONCAT, т.е. в такой выборке мы теряем все значения sp_id, кроме самого первого, но в @parent_values благодаря тому же GROUP_CONCAT попадают дети всех sp_id, даже тех, чьи значения мы «потеряли»).
3 |
12 |
NULL |
NULL |
Теперь надо найти детей вершин... NULL, т.е. никаких: конец алгоритма.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 507/545
Пример 46: формирование и анализ иерархических структур
Переходим к MS SQL Server. Здесь решение будет совершенно иным, т.к., вопервых, данная СУБД не поддерживает часть синтаксиса, необходимого для эмуляции решения MySQL, а во-вторых, с использованием возможностей MS SQL Server эта задача решается проще (несмотря на то, что кода будет больше).
MS SQL |
Решение 7.1.1 |
1 CREATE.a FUNCTION GET ALL CHILDREN @parent INT, @mode VARCHAR 50))
2RETURNS @all children TABLE
3(
4id VARCHAR(max)
5)
6AS
7BEGIN
8 IF @mode = 'TABLE')
9BEGIN
10WITH [tree] ([sp_id], [sp_parent])
11AS
12(
13SELECT [sp id]
14 |
|
[sp_parent] |
15 |
FROM |
[site_pages] |
16 |
WHERE |
[sp id] = @parent |
17UNION ALL
18SELECT [inner] [sp id]
19 |
|
[inner] [sp_parent] |
20 |
FROM |
[site_pages] AS [inner] |
21 |
|
JOIN [tree] |
22 |
|
ON [inner] [sp_parent] = [tree] [sp_id] |
23)
24INSERT @all children
25SELECT CAST [sp id] AS VARCHAR)
26 FROM [tree]
27WHERE [sp id] != @parent
28END
29ELSE
30BEGIN
31WITH [tree] ([sp_id] [sp_parent])
32AS
33(
34SELECT [sp id],
35 |
|
[sp_parent] |
36 |
FROM |
[site_pages] |
37 |
WHERE |
[sp id] = 2 |
38UNION ALL
39SELECT [inner] [sp id],
40 |
|
[inner] [sp_parent] |
41 |
FROM |
[site_pages] AS [inner] |
42 |
|
JOIN [tree] |
43 |
|
ON [inner] [sp_parent] = [tree] [sp_id] |
44)
45INSERT @all children
46SELECT STUFF((SELECT ',' + CAST [sp id] AS VARCHAR)
47 |
FROM |
[tree] |
48 |
|
WHERE [sp id] != 2 |
49 |
FOR XML PATH(''), TYPE) value('.', 'nvarchar (max) '), |
|
50 |
1, 1, |
''); |
51END;
52RETURN
53END;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 508/545
Пример 46: формирование и анализ иерархических структур
Проверить работоспособность и корректность представленного решения можно выполнением следующего кода. Первый запрос возвратит результат в том виде, в котором это требуется по условию задачи (список идентификаторов, разделённых запятыми), второй запрос возвратит таблицу из одной колонки, где каждый идентификатор будет расположен в отдельной строке.
MS SQL Решение 7.1.1.a (пример использования функции) |
|
|||
|
1 |
SELECT * FROM |
GET_ALL_CHILDREN 2 |
'STRING') |
2 |
SELECT * FROM GET ALL CHILDREN 2, |
'TABLE'); |
|
|
Итак, рассмотрим решение. Мы создали именно табличную функцию затем, чтобы иметь возможность возвращать данные не только в виде списка идентификаторов, перечисленных через запятую, но и в виде таблицы из одной колонки, строками которой являются идентификаторы.
Вслучае, когда нам всё же нужен список идентификаторов, перечисленных через запятую, мы по-прежнему возвращаем таблицу из одной колонки, но в ней будет ровно одна строка, содержащая весь наш список.
Воснове решения лежит т.н. рекурсивное общее табличное выражение41. Рассмотрим его отдельно.
MS SQL |
Решение 7.1.1 .a (рекурсивное общее табличное выражение) |
|
1 |
WITH [tree] ([sp_id], [sp_parent]) |
||
2 |
AS |
|
|
3 |
( |
|
|
4 |
SELECT [sp_id], |
||
5 |
|
[sp_parent] |
|
6 |
FROM |
[site_pages] |
|
|
WHERE |
[sp_id] = {родительская_вершина} |
|
8 |
UNION ALL |
|
|
9 |
SELECT [inner] [sp_id] |
||
10 |
|
[inner] [sp_parent] |
|
11 |
FROM |
[site_pages] AS [inner] |
|
12 |
|
JOIN |
[tree] |
13 |
|
ON |
[inner] [sp_parent] = [tree] [sp_id] |
14 |
) |
|
|
15 |
SELECT |
[sp_id] |
|
16 |
FROM |
[tree] |
|
17 |
WHERE |
[sp id] != {родительская вершина} |
|
Рекурсивное общее табличное выражение должно содержать две части:
•в первой части (строки 4-7) происходит выборка родительской записи (для которой мы будем искать дочерние);
•во второй части (строки 8-13) происходит рекурсивное обращение к результату выполнения общего табличного выражения, что и позволяет нам получить всё поддерево заданной вершины.
Поскольку первая часть (до оператора UNION) является обязательной, а по
условию задачи идентификатор самой родительской вершины не должен попадать в выборку, мы исключаем попадание соответствующей записи в итоговую выборку условием в строке 17.
Полученное рекурсивно общее табличное выражение мы разместили в строках 10-27 кода функции, обеспечив вставку его результатов (строка 24) в результирующую таблицу, которую возвращает функция.
В строках 31-50 кода функции находится то же самое рекурсивное общее табличное выражение, но при выборке результатов его выполнения мы применяем приём, подробно описанный в решении{72} задачи 2.2.2.a{71} для эмуляции функции
41 https://msdn.microsoft.com/en-us/library/ms175972%28v=sql.110%29.aspx
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 509/545