Рекурсивное CTE-выражение SQL Server

В этом руководстве вы узнаете, как использовать рекурсивное CTE-выражение SQL Server для запроса иерархических данных.

Введение в рекурсивное CTE SQL Server

Рекурсивное общее табличное выражение(CTE) — это CTE, которое ссылается на себя. При этом CTE многократно выполняется, возвращает подмножества данных, пока не вернет полный набор результатов.

Рекурсивное CTE полезно при запросе иерархических данных, таких как организационные диаграммы, где один сотрудник подчиняется менеджеру, или многоуровневые спецификации материалов, когда продукт состоит из многих компонентов, а каждый компонент сам по себе также состоит из многих других компонентов.

Ниже показан синтаксис рекурсивного CTE:

WITH expression_name(column_list)
AS
(
    -- Anchor member
    initial_query  
    UNION ALL
    -- Recursive member that references expression_name.
    recursive_query  
)
-- references expression name
SELECT *
FROM   expression_name

В общем случае рекурсивное CTE состоит из трех частей:

1. Начальный запрос, который возвращает базовый набор результатов CTE. Начальный запрос называется якорным членом.
2. Рекурсивный запрос, который ссылается на общее табличное выражение, поэтому он называется рекурсивным членом. Рекурсивный член объединяется с якорным членом с помощью оператора UNION ALL.
3. Условие завершения, указанное в рекурсивном члене, которое завершает выполнение рекурсивного члена.

Порядок выполнения рекурсивного CTE следующий:

• Сначала выполните якорный элемент, чтобы сформировать базовый набор результатов(R0), используйте этот результат для следующей итерации.
• Во-вторых, выполнить рекурсивный элемент с входным набором результатов из предыдущей итерации(Ri-1) и вернуть набор промежуточных результатов(Ri) до тех пор, пока не будет выполнено условие завершения.
• В-третьих, объедините все наборы результатов R0, R1, … Rn с помощью оператора UNION ALL для получения окончательного набора результатов.

Следующая блок-схема иллюстрирует выполнение рекурсивного CTE:

Примеры рекурсивных CTE-выражений SQL Server

Давайте рассмотрим несколько примеров использования рекурсивных CTE.

A) Простой пример рекурсивного CTE-выражения SQL Server

В этом примере рекурсивное CTE используется для возврата дней недели с понедельника по субботу:

WITH cte_numbers(n, weekday) 
AS(
    SELECT 
        0, 
        DATENAME(DW, 0)
    UNION ALL
    SELECT    
        n + 1, 
        DATENAME(DW, n + 1)
    FROM    
        cte_numbers
    WHERE n < 6
)
SELECT 
    weekday
FROM 
    cte_numbers;

Вот результат:

В этом примере:

ФункцияDATENAME() возвращает название дня недели на основе номера дня недели.

Ведущий участник возвращается в понедельник

SELECT 
    0, 
    DATENAME(DW, 0)

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

    SELECT    
        n + 1, 
        DATENAME(DW, n + 1)
    FROM    
        cte_numbers
    WHERE n < 6

Условие в предложении WHERE является условием завершения, которое останавливает выполнение рекурсивного члена, когда n равно 6.

n < 6

Б) Использование рекурсивного CTE SQL Server для запроса иерархических данных

См. следующую таблицу sales.staffs из примера базы данных :

В этой таблице персонал подчиняется нулю или одному менеджеру. Менеджер может иметь ноль или более сотрудников. У топ-менеджера нет менеджера. Связь указывается в значениях столбца manager_id. Если персонал не подчиняется ни одному сотруднику(в случае топ-менеджера), значение в manager_id равно NULL.

В этом примере рекурсивное CTE используется для получения всех подчиненных топ-менеджера, у которого нет менеджера(или значение в столбце manager_id равно NULL):

WITH cte_org AS(
    SELECT       
        staff_id, 
        first_name,
        manager_id
    FROM       
        sales.staffs
    WHERE manager_id IS NULL
    UNION ALL
    SELECT 
        e.staff_id, 
        e.first_name,
        e.manager_id
    FROM 
        sales.staffs e
        INNER JOIN cte_org o 
            ON o.staff_id = e.manager_id
)
SELECT * FROM cte_org;

Вот что получилось:

В этом примере якорный член получает топ-менеджера, а рекурсивный запрос возвращает подчиненных топ-менеджеров и подчиненных топ-менеджера и т. д.

В этом руководстве вы узнали, как использовать рекурсивное CTE-выражение SQL Server для запроса иерархических данных.