Рекурсивные запросы, использующие обобщенные табличные выражения

Обобщенное табличное выражение (ОТВ) имеет значительное преимущество, так как оно может ссылаться на себя, создавая рекурсивное ОТВ. Рекурсивное ОТВ является выражением, в котором начальное ОТВ многократно выполняется, чтобы возвращать подмножество данных до тех пор, пока не получится конечный результирующий набор.

В SQL Server 2005 запрос именуется рекурсивным, если он ссылается на рекурсивное ОТВ. Обычно рекурсивные запросы используются для возвращения иерархических данных, например: отображение сотрудников в структуре организации или данных в сценарии ведомости материалов, в котором родительский продукт состоит из одного или более компонентов, и эти компоненты могут, в свою очередь, состоять из вспомогательных компонентов или являться компонентами других родителей.

Рекурсивное ОТВ может существенно упростить код, требуемый для запуска рекурсивного запроса в рамках инструкций SELECT, INSERT, UPDATE, DELETE или CREATE VIEW. В более ранних версиях SQL Server, чтобы контролировать поток рекурсивных шагов, рекурсивный запрос обычно требует использование временных таблиц, курсоров и логики. Дополнительные сведения об обобщенных табличных выражениях см. в разделе Применение обобщенных табличных выражений.

Структура рекурсивного ОТВ

Структура рекурсивного ОТВ в Transact-SQL аналогична рекурсивным процедурам в других языках программирования. Но рекурсивные процедуры в других языках возвращают скалярное значение, а рекурсивное ОТВ может возвращать несколько строк.

Рекурсивное ОТВ состоит из трех элементов.

1. Вызов процедуры.
   Первый вызов рекурсивного ОТВ состоит из одного или более параметров CTE_query_definitions, соединенных операторами UNION ALL, UNION, EXCEPT или INTERSECT. Так как данные определения запроса формируют базовый результирующий набор структуры ОТВ, они называются закрепленными элементами.
   Параметры CTE_query_definitions считаются закрепленными элементами, если они не ссылаются на само ОТВ. Все определения запросов закрепленных элементов должны размещаться перед определением первого рекурсивного элемента, а оператор UNION ALL должен использоваться для соединения последнего закрепленного элемента с первым рекурсивным элементом.
2. Рекурсивный вызов процедуры.
   Рекурсивный вызов включает в себя от одного или более параметров CTE_query_definitions, которые соединены операторами UNION ALL, ссылающимися на само ОТВ. Данные определения запросов называются рекурсивными элементами.
3. Проверка завершения.
   Проверка завершения происходит неявно; рекурсия останавливается, если из предыдущего вызова не вернулась ни одна строка.

Примечание.

Неправильно составленное рекурсивное ОТВ может привести к бесконечному циклу. Например, если запрос рекурсивного элемента возвращает одинаковые значения для родительского столбца и столбца потомка, то образуется бесконечный цикл. При тестировании результатов рекурсивного запроса можно ограничить число уровней рекурсии, допустимое для конкретных инструкций, используя подсказку MAXRECURSION и значение от 0 до 32 767 в предложении OPTION инструкций INSERT, UPDATE, DELETE или SELECT. Дополнительные сведения см. в разделах Подсказка в запросе (Transact-SQL) и WITH общее_табличное_выражение (Transact-SQL).

Псевдокод и семантика

Структура рекурсивного ОТВ должна содержать минимум один закрепленный элемент и один рекурсивный элемент. Следующий псевдокод отображает компоненты простого рекурсивного ОТВ, которое содержит один закрепленный элемент и один рекурсивный элемент.

WITH cte_name ( column_name [,...n] )

AS

(

CTE_query_definition -- Anchor member is defined.

UNION ALL

CTE_query_definition -- Recursive member is defined referencing cte_name.

)

-- Statement using the CTE

SELECT *

FROM cte_name

Рекурсивное выполнение имеет следующую семантику:

1. разбиение ОТВ на закрепленный и рекурсивный элементы;
2. запуск закрепленных элементов с созданием первого вызова или базового результирующего набора (T0);
3. запуск рекурсивных элементов, где Ti — это вход, а Ti+1 — это выход;
4. повторение шага 3 до тех пор, пока не вернется пустой набор;
5. возвращение результирующего набора. Результирующий набор получается с помощью инструкции UNION ALL от T0 до Tn.

Пример

Следующий пример показывает семантику структуры рекурсивного ОТВ, возвращая иерархический список служащих компании Adventure Works Cycles, начиная с высшего должностного лица. Инструкция, выполняющая ОТВ, сокращает результирующий набор до служащих отдела исследований и разработок. За примером следует анализ выполнения кода.

USE AdventureWorks;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
-- Anchor member definition
    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, 
        0 AS Level
    FROM HumanResources.Employee AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
    WHERE ManagerID IS NULL
    UNION ALL
-- Recursive member definition
    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
        Level + 1
    FROM HumanResources.Employee AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
    INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
    ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Research and Development' OR Level = 0;
GO

Анализ примера кода

1. Рекурсивное ОТВ DirectReports определяет один закрепленный элемент и один рекурсивный элемент.

2. Закрепленный элемент возвращает базовый результирующий набор T0. Это самое главное должностное лицо компании; значит, этот служащий не отчитывается перед управляющим.
   Ниже приведен результирующий набор, возвращенный закрепленным элементом.

   ManagerID EmployeeID Title                                   Level
   --------- ---------- --------------------------------------- ------
   NULL      109        Chief Executive Officer                 0
   

3. Рекурсивный элемент возвращает прямых подчиненных служащего в результирующий набор закрепленного элемента. Это получается при соединении таблицы Employee и DirectReports ОТВ. Это ссылка на само ОТВ, которое устанавливает рекурсивный вызов. В зависимости от служащего в ОТВ DirectReports в качестве входа (Ti), соединение (Employee.ManagerID = DirectReports.EmployeeID) возвращает выход (Ti+1) — это служащие, чьим управляющим является (Ti). Таким образом, первый шаг цикла рекурсивного элемента возвращает данный результирующий набор:

   ManagerID EmployeeID Title                                   Level
   --------- ---------- --------------------------------------- ------
   109       12         Vice President of Engineering           1
   

4. Рекурсивный элемент постоянно активируется. Второй шаг цикла рекурсивного элемента использует однострочный результирующий набор в шаге 3 (содержащий EmployeeID``12) в качестве входного значения и возвращает следующий результирующий набор:

   ManagerID EmployeeID Title                                   Level
   --------- ---------- --------------------------------------- ------
   12        3          Engineering Manager                     2
   

   Третий шаг цикла рекурсивного элемента использует вышеупомянутый однострочный результирующий набор (содержащий EmployeeID``3)) в качестве входного значения и возвращает данный результирующий набор:

   ManagerID EmployeeID Title                                   Level
   --------- ---------- --------------------------------------- ------
   3         4          Senior Tool Designer                    3
   3         9          Design Engineer                         3
   3         11         Design Engineer                         3
   3         158        Research and Development Manager        3
   3         263        Senior Tool Designer                    3
   3         267        Senior Design Engineer                  3
   3         270        Design Engineer                         3
   

   Четвертый шаг цикла рекурсивного элемента использует предыдущий результирующий набор для EmployeeID значений 4, 9, 11, 158, 263, 267 и 270 в качестве входного значения.
   Данный процесс повторяется до тех пор, пока рекурсивный элемент не вернет пустой результирующий набор.

5. Конечный результирующий набор, возвращенный запущенным запросом, представляет собой объединение всех результирующих наборов, сформированных закрепленным и рекурсивным элементами.
   Ниже приведен полный результирующий набор, возвращенный примером.

   ManagerID EmployeeID Title                                   Level
   --------- ---------- --------------------------------------- ------
   NULL      109        Chief Executive Officer                 0
   109       12         Vice President of Engineering           1
   12        3          Engineering Manager                     2
   3         4          Senior Tool Designer                    3
   3         9          Design Engineer                         3
   3         11         Design Engineer                         3
   3         158        Research and Development Manager        3
   3         263        Senior Tool Designer                    3
   3         267        Senior Design Engineer                  3
   3         270        Design Engineer                         3
   263       5          Tool Designer                           4
   263       265        Tool Designer                           4
   158       79         Research and Development Engineer       4
   158       114        Research and Development Engineer       4
   158       217        Research and Development Manager        4
   (15 row(s) affected)
   

См. также

Основные понятия

Применение обобщенных табличных выражений

Другие ресурсы

WITH общее_табличное_выражение (Transact-SQL)
Подсказка в запросе (Transact-SQL)
INSERT (Transact-SQL)
UPDATE (Transact-SQL)
DELETE (Transact-SQL)
EXCEPT и INTERSECT (Transact-SQL)

Справка и поддержка

Получение помощи по SQL Server 2005