Рекурсивные запросы с использованием общих выражений таблицы (Transact-SQL)

Applies to:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL analytics endpoint in Microsoft FabricWarehouse in Microsoft FabricSQL database in Microsoft Fabric

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

Запрос называется рекурсивным запросом при ссылке на рекурсивный CTE. Возврат иерархических данных — это частое использование рекурсивных запросов. Например, отображение сотрудников в организационной диаграмме или данные в счете о материалах сценария, в котором родительский продукт имеет один или несколько компонентов, и эти компоненты могут иметь подкомпоненты или могут быть компонентами других родителей.

Рекурсивный CTE может значительно упростить код, необходимый для выполнения рекурсивного запроса в операторе SELECT, или INSERTUPDATEDELETECREATE VIEW инструкции. В более ранних версиях SQL Server рекурсивный запрос обычно требует использования временных таблиц, курсоров и логики для управления потоком рекурсивных шагов. For more information about common table expressions, see WITH common_table_expression.

Структура рекурсивного CTE

Структура рекурсивного CTE в Transact-SQL аналогична рекурсивным подпрограммам на других языках программирования. Хотя рекурсивная подпрограмма на других языках возвращает скалярное значение, рекурсивный CTE может возвращать несколько строк.

Рекурсивный CTE состоит из трех элементов:

1. Вызов подпрограммы.

   Первое вызов рекурсивного CTE состоит из одного или нескольких определений запросов CTE, присоединенных к операторам , UNION ALLUNIONили EXCEPT операторамINTERSECT. Так как эти определения запросов образуют базовый результирующий набор структуры CTE, они называются элементами привязки.

   Определения запросов CTE считаются элементами привязки, если они не ссылаются на сам CTE. Все определения запросов к элементу привязки должны быть расположены перед первым рекурсивным определением члена, и UNION ALL оператор должен использоваться для присоединения последнего элемента привязки к первому рекурсивному элементу.

2. Рекурсивное вызов подпрограммы.

   Рекурсивное вызов включает одно или несколько определений запросов CTE, присоединенных операторами UNION ALL , ссылающимися на сам CTE. Эти определения запросов называются рекурсивными элементами.

3. Termination check.

   Проверка завершения неявна; Рекурсия останавливается, если из предыдущего вызова не возвращаются строки.

Дополнительные сведения можно найти здесь

• Query hints
• WITH common_table_expression
• Recursive CTEs

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

Рекурсивная структура CTE должна содержать по крайней мере один элемент привязки и один рекурсивный элемент. В следующем псевдокоде показаны компоненты простого рекурсивного CTE, содержащего один элемент привязки и один рекурсивный элемент.

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. Разделение выражения CTE на привязку и рекурсивные элементы.
2. Запустите элементы привязки, создающие первый вызов или базовый результирующий набор (T0).
3. Запустите рекурсивные элементы с Ti входными данными и Ti + 1 в качестве выходных данных.
4. Повторите шаг 3, пока не будет возвращен пустой набор.
5. Возвращает результирующий набор. Это то UNION ALL , T0 чтобы Tn.

Examples

В следующем примере показана семантика рекурсивной структуры CTE, возвращая иерархический список сотрудников, начиная с самого высокого ранга сотрудника в AdventureWorks2022 базе данных. Пошаговое руководство по выполнению кода следует примеру.

Создайте таблицу сотрудников:

CREATE TABLE dbo.MyEmployees
(
    EmployeeID SMALLINT NOT NULL,
    FirstName NVARCHAR (30) NOT NULL,
    LastName NVARCHAR (40) NOT NULL,
    Title NVARCHAR (50) NOT NULL,
    DeptID SMALLINT NOT NULL,
    ManagerID INT NULL,
    CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)
);

Заполните таблицу значениями:

INSERT INTO dbo.MyEmployees
VALUES
    (1, N'Ken', N'Sánchez', N'Chief Executive Officer', 16, NULL),
    (273, N'Brian', N'Welcker', N'Vice President of Sales', 3, 1),
    (274, N'Stephen', N'Jiang', N'North American Sales Manager', 3, 273),
    (275, N'Michael', N'Blythe', N'Sales Representative', 3, 274),
    (276, N'Linda', N'Mitchell', N'Sales Representative', 3, 274),
    (285, N'Syed', N'Abbas', N'Pacific Sales Manager', 3, 273),
    (286, N'Lynn', N'Tsoflias', N'Sales Representative', 3, 285),
    (16, N'David', N'Bradley', N'Marketing Manager', 4, 273),
    (23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

USE AdventureWorks2008R2;
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 dbo.MyEmployees AS e
         INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
             ON e.EmployeeID = edh.BusinessEntityID
            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 dbo.MyEmployees AS e
         INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
             ON e.EmployeeID = edh.BusinessEntityID
            AND edh.EndDate IS NULL
         INNER JOIN DirectReports AS d
             ON e.ManagerID = d.EmployeeID)
-- Statement that executes the CTE
SELECT ManagerID,
       EmployeeID,
       Title,
       DeptID,
       Level
FROM DirectReports
     INNER JOIN HumanResources.Department AS dp
         ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Sales and Marketing'
      OR Level = 0;
GO

Пример пошагового руководства по коду

Рекурсивный CTE DirectReportsопределяет один элемент привязки и один рекурсивный элемент.

Элемент привязки возвращает базовый результирующий набор T0. Это самый высокопоставленный сотрудник компании. То есть сотрудник, который не сообщает руководителю.

Ниже приведен результирующий набор, возвращаемый элементом привязки:

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

Рекурсивный член возвращает прямых подчиненных сотрудника в результирующем наборе элементов привязки. Это достигается операцией соединения между таблицей Employee и DirectReports CTE. Это ссылка на сам CTE, который устанавливает рекурсивный вызов. На основе сотрудника в CTE DirectReports в качестве входных данных (), соединение (Ti) возвращается в виде выходных данных (MyEmployees.ManagerID = DirectReports.EmployeeIDTi+ 1), сотрудников, имеющих (Ti) в качестве руководителя.

Таким образом, первая итерация рекурсивного элемента возвращает этот результирующий набор:

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
1         273        Vice President of Sales       1

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

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
273       16         Marketing Manager             2
273       274        North American Sales Manager  2
273       285        Pacific Sales Manager         2

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

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
16        23         Marketing Specialist          3
274       275        Sales Representative          3
274       276        Sales Representative          3
285       286        Sales Representative          3

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

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

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
NULL      1          Chief Executive Officer       0
1         273        Vice President of Sales       1
273       16         Marketing Manager             2
273       274        North American Sales Manager  2
273       285        Pacific Sales Manager         2
16        23         Marketing Specialist          3
274       275        Sales Representative          3
274       276        Sales Representative          3
285       286        Sales Representative          3

Related content

• WITH common_table_expression (Transact-SQL)
• Подсказки запросов (Transact-SQL)
• INSERT (Transact-SQL)
• UPDATE (Transact-SQL)
• DELETE (Transact-SQL)
• EXCEPT и INTERSECT (Transact-SQL)
• Recursive CTEs