Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 46: формирование и анализ иерархических структур
Рисунок 7.b — Визуальное представление карты сайта
Теперь, когда все данные подготовлены, мы можем переходить к задачам.
Задача 7.1.1.a{476}: создать функцию, возвращающую список идентификаторов всех дочерних вершин заданной вершины (например, идентификаторов всех подстраниц страницы «Читателям»).
Задача 7.1.1.b{482}: написать запрос для показа всего поддерева заданной вершины дерева, включая саму родительскую вершину (например, всех подстраниц страницы «Читателям», включая саму эту страницу).
Задача 7.1.1.c{485}: написать функцию, возвращающую список идентификаторов вершин на пути от заданной вершины к корню дерева (например, идентификаторов всех вершин на пути от страницы «Архивная» к странице «Главная»).
Ожидаемый результат 7.1.1.a.
Для вершины с идентификатором 2 (страница «Читателям») функция должна возвратить следующие данные:
shildren_of_2
5,6,12,13,14
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 475/545
Пример 46: формирование и анализ иерархических структур
Ожидаемый результат 7.1.1.b.
Для вершины с идентификатором 2 (страница «Читателям») запрос должен возвратить следующие данные:
sp_id |
sp_name |
2 |
Читателям |
5 |
Новости |
6 |
Статистика |
12 |
Текущая |
13 |
Архивная |
14 |
Неофициальная |
Ожидаемый результат 7.1.1.c.
Для вершины с идентификатором 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 Стр: 476/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_node) |
|
17 |
|
|
|
AS `initialisation` |
18 |
|
WHERE |
`sp_id` IN (@parent_values) |
|
19) AS `data`;
20RETURN result;
21END$$
22DELIMITER ;
Проверить работоспособность и корректность представленного решения можно выполнением следующего кода.
MySQL Решение 7.1.1.a (пример использования функции)
1-- Проверка работоспособности функции:
2SELECT GET_ALL_CHILDREN(2) AS `shildren_of_2`;
3
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 Стр: 477/545
Пример 46: формирование и анализ иерархических структур
|
MySQL |
|
Решение 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_CONCAT, т.е. в такой выборке мы теряем все значения 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 Стр: 478/545
Пример 46: формирование и анализ иерархических структур
Переходим к MS SQL Server. Здесь решение будет совершенно иным, т.к., во-первых, данная СУБД не поддерживает часть синтаксиса, необходимого для эмуляции решения MySQL, а во-вторых, с использованием возможностей MS SQL Server эта задача решается проще (несмотря на то, что кода будет больше).
MS SQL Решение 7.1.1.a (код функции)
1CREATE FUNCTION GET_ALL_CHILDREN(@parent INT, @mode VARCHAR(50))
2RETURNS @all_children TABLE
3(
4id VARCHAR(max)
5)
6AS
7BEGIN
8IF (@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]
37WHERE [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 Стр: 479/545