Id_father integer foreign key references Tree (id),

NAME VARCHAR(16));

Заполним её данными INSERT INTO Tree

Values (1, null, 'all'), (2, 1, 'sea'), (3, 1, 'earth'),

(4, 1, 'AIR'), (5, 2, 'SUBMARINE'),

(6, 2, 'BOAT'), (7, 3, 'CAR'),

(8, 3, 'TWO WHEELES'), (9, 3, 'TRUCK'),

(10, 4, 'ROCKET'), (11, 4, 'PLANE'),

(12, 8, 'MOTORCYCLE'), (13, 8, 'BYCYCLE');

Обычно иерархия схематизируется авто-ссылкой, которая имеет место и здесь: внешний ключ ссылается на первичный ключ той же таблицы. Имеющиеся данные можно трактовать следующим образом:

ALL |--SEA |     |--SUBMARINE |     |--BOAT |--EARTH |     |--CAR |     |--TWO WHEELES |     |     |--MOTORCYCLE |     |     |--BYCYCLE |     |--TRUCK |--AIR      |--ROCKET      |--PLANE

Построим запрос. Необходимо узнать, откуда пришел МОТОЦИКЛ (MOTORCYCLE). Другими словами, требуется найти всех предков "МОТОЦИКЛА". Начать следует со строки данных, которая содержат motorcycle:

Результат
NAME	ID_FATHER
MOTORCYCLE	8

SELECT NAME, ID_FATHER
FROM Tree

WHERE NAME = 'MOTORCYCLE';

Мы должны иметь родительский ID, чтобы перейти к следующему шагу. Второй запрос, который делает этот следующий шаг, должен быть написан подобно следующему:

SELECT NAME, ID_FATHER FROM Tree;

Запросы отличаются только тем, что мы не задаем фильтр WHERE для перехода к следующему шагу. Затем мы должны объединить эти два запроса с помощью UNION ALL, что определит пошаговый метод:

SELECT NAME, ID_FATHER FROM Tree

WHERE NAME = 'MOTORCYCLE'

UNION ALL SELECT NAME, ID_FATHER FROM Tree;

Теперь разместим все это в CTE:

WITH

tree_CTE (data, id)

AS (SELECT NAME, ID_FATHER

FROM Tree

WHERE NAME = 'MOTORCYCLE'

UNION ALL SELECT NAME, ID_FATHER FROM Tree

);

Теперь мы вплотную подошли к рекурсии. Последний шаг должен сделать цикл, чтобы организовать выполнение пошагового метода. Это делается при использовании имени CTE в качестве таблицы внутри SQL-запроса CTE. В нашем случае мы должны соединить второй запрос CTE с самим CTE, организовав цепочку по tree_CTE.id = (второй запрос).ID. Это можно сделать следующим образом:

WITH

tree_CTE (data, id)

AS (SELECT NAME, ID_FATHER

FROM Tree

WHERE NAME = 'MOTORCYCLE'

UNION ALL SELECT NAME, ID_FATHER FROM Tree JOIN tree_CTE ON tree_CTE.id = Tree.ID

)

Результат
data	id
MOTORCYCLE	8
TWO WHEELES	3
EARTH	1
ALL	NULL

SELECT * FROM tree_CTE;

Что остановило рекурсивный процесс? Факт, что больше нет звеньев цепочки, когда достигается значение id "NULL", что в нашем примере означает случай достижения "ALL".

Отметим, что по неясным причинам MS SQL Server 2005 не допускает ключевого слова RECURSIVE после слова WITH, которое вводит CTE.

Еще одна важная вещь, связанная со структурированными в форме деревьев данными, - это визуализация данных в виде дерева ..., что означает наличие отступов в иерархии при извлечении данных. Возможно ли это? Да, конечно. Это может быть сделано вычислением пути внутри рекурсии и уровня вложенности. Вот пример такого запроса:

WITH tree_CTE (data, id, level, pathstr)

AS (SELECT NAME, ID, 0, cast(' ' as varchar(MAX))

FROM Tree

WHERE ID_FATHER IS NULL

UNION ALL SELECT NAME, Tree.ID,

tree_CTE.level+4, tree_CTE .pathstr + ' ' +Tree.NAME

FROM Tree JOIN tree_CTE ON tree_CTE.id = Tree. ID_FATHER

)

SELECT SPACE(level) + data as data, id, level, pathstr FROM tree_CTE;

Результат
data	id	level	pathstr
ALL	1	0
SEA	2	4	SEA
EARTH	3	4	EARTH
AIR	4	4	AIR
ROCKET	10	8	AIR ROCKET
PLANE	11	8	AIR PLANE
CAR	7	8	EARTH CAR
TWO WHEELES	8	8	EARTH TWO WHEELES
TRUCK	9	8	EARTH TRUCK
MOTORCYCLE	12	12	EARTH TWO WHEELES MOTORCYCLE
BYCYCLE	13	12	EARTH TWO WHEELES BYCYCLE
SUBMARINE	5	8	SEA SUBMARINE
BOAT	6	8	SEA BOAT

Мы использовали новый тип данных в SQL 2005, который называется VARCHAR (MAX), поскольку мы не знаем максимального количества символов, которое потребуется при конкатенации VARCHAR (16) в рекурсивном запросе, который может оказаться очень глубоким.