Compartir a través de

Consultas recursivas mediante expresiones de tabla comunes (Transact-SQL)

Aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSistema de Plataforma de Analítica (PDW)Base de datos SQL en Microsoft Fabric

Una expresión de tabla común (CTE) proporciona la ventaja significativa de poder hacer referencia a sí misma, lo que crea un CTE recursivo. Un CTE recursivo es uno en el que se ejecuta repetidamente un CTE inicial para devolver subconjuntos de datos hasta que se obtiene el conjunto de resultados completo.

Una consulta se conoce como una consulta recursiva cuando hace referencia a un CTE recursivo. Devolver datos jerárquicos es un uso común de consultas recursivas. Por ejemplo, mostrar empleados en un organigrama o datos en un escenario de lista de materiales en el que un producto primario tiene uno o varios componentes y esos componentes pueden tener subcomponentes, o pueden ser componentes, de otros elementos primarios.

Un CTE recursivo puede simplificar considerablemente el código necesario para ejecutar una consulta recursiva dentro de una SELECTinstrucción , INSERT, UPDATE, DELETEo CREATE VIEW . En versiones anteriores de SQL Server, una consulta recursiva normalmente requiere usar tablas temporales, cursores y lógica para controlar el flujo de los pasos recursivos. Para obtener más información sobre las expresiones de tabla comunes, vea WITH common_table_expression.

En Microsoft Fabric, Fabric Data Warehouse y el punto de conexión de SQL Analytics admiten CTE estándar, secuenciales y anidados, pero no CTE recursivos.

Estructura de un CTE recursivo

La estructura del CTE recursivo en Transact-SQL es similar a las rutinas recursivas en otros lenguajes de programación. Aunque una rutina recursiva en otros lenguajes devuelve un valor escalar, un CTE recursivo puede devolver varias filas.

Un CTE recursivo consta de tres elementos:

  1. Invocación de la rutina.

    La primera invocación del CTE recursivo consta de una o varias definiciones de consulta de CTE unidas por UNION ALLoperadores , UNION, EXCEPTo INTERSECT . Dado que estas definiciones de consulta forman el conjunto de resultados base de la estructura CTE, se denominan miembros de anclaje.

    Las definiciones de consulta de CTE se consideran miembros de anclaje a menos que hagan referencia al propio CTE. Todas las definiciones de consulta de miembro de delimitador deben colocarse antes de la primera definición de miembro recursivo y se debe usar un UNION ALL operador para unir el último miembro delimitador con el primer miembro recursivo.

  2. Invocación recursiva de la rutina.

    La invocación recursiva incluye una o varias definiciones de consulta de CTE unidas por UNION ALL operadores que hacen referencia al propio CTE. Estas definiciones de consulta se conocen como miembros recursivos.

  3. Comprobación de terminación.

    La comprobación de terminación es implícita; la recursividad se detiene cuando no se devuelve ninguna fila de la invocación anterior.

Note

Un CTE recursivo compuesto incorrectamente podría provocar un bucle infinito. Por ejemplo, si la definición de la consulta del miembro recursivo devuelve los mismos valores para las columnas primarias y secundarias, se crea un bucle infinito. Al probar los resultados de una consulta recursiva, puede limitar el número de niveles de recursión permitidos para una instrucción específica mediante la MAXRECURSION sugerencia y un valor entre 0 y 32 767 en la cláusula de la OPTIONINSERTinstrucción , UPDATE, DELETE, o SELECT .

Para obtener más información, consulte:

Pseudocódigo y semántica

La estructura CTE recursiva debe contener al menos un miembro de anclaje y un miembro recursivo. En el pseudocódigo siguiente se muestran los componentes de un CTE recursivo simple que contiene un único miembro de delimitador y un único miembro recursivo.

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

La semántica de la ejecución recursiva es la siguiente:

  1. Divida la expresión CTE en miembros delimitadores y recursivos.
  2. Ejecute los miembros de anclaje creando la primera invocación o conjunto de resultados base (T0).
  3. Ejecute los miembros recursivos con Ti como entrada y Ti + 1 como salida.
  4. Repita el paso 3 hasta que se devuelva un conjunto vacío.
  5. Devuelve el conjunto de resultados. Se trata de un UNION ALL de T0 a Tn.

Examples

En el ejemplo siguiente se muestra la semántica de la estructura CTE recursiva devolviendo una lista jerárquica de empleados, empezando por el empleado de mayor clasificación, en la AdventureWorks2025 base de datos. Un tutorial de la ejecución de código sigue el ejemplo.

Cree una tabla de empleados:

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)
);

Rellene la tabla con valores:

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

Tutorial de código de ejemplo

El CTE recursivo, DirectReports, define un miembro de delimitador y un miembro recursivo.

El miembro de delimitador devuelve el conjunto T0de resultados base . Este es el empleado de mayor clasificación de la empresa. Es decir, un empleado que no informa a un administrador.

Este es el conjunto de resultados devuelto por el miembro de anclaje:

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

El miembro recursivo devuelve los subordinados directos del empleado en el conjunto de resultados del miembro de anclaje. Esto se logra mediante una operación de combinación entre la tabla Employee y el DirectReports CTE. Es esta referencia al propio CTE que establece la invocación recursiva. En función del empleado en el CTE DirectReports como entrada (Ti), la unión (MyEmployees.ManagerID = DirectReports.EmployeeID) devuelve como salida (Ti + 1), los empleados que tienen (Ti) como administrador.

Por lo tanto, la primera iteración del miembro recursivo devuelve este conjunto de resultados:

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

El miembro recursivo se activa repetidamente. La segunda iteración del miembro recursivo usa el conjunto de resultados de una sola fila en el paso 3 (que contiene un EmployeeID valor de 273) como valor de entrada y devuelve este conjunto de resultados:

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

La tercera iteración del miembro recursivo usa el conjunto de resultados anterior como valor de entrada y devuelve este conjunto de resultados:

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

El conjunto de resultados final devuelto por la consulta en ejecución es la unión de todos los conjuntos de resultados generados por los miembros delimitadores y recursivos.

Este es el conjunto de resultados.

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

Contenido relacionado

  • Last updated on