Consultas recursivas mediante expresiones de tabla comunes

Artículo
12/15/2008

Una expresión de tabla común (CTE) ofrece la gran ventaja de poder hacer referencia a sí misma, creando así una CTE recursiva. Una CTE recursiva es aquélla en la que una CTE inicial se ejecuta varias veces para devolver subconjuntos de datos hasta que se obtenga el conjunto de resultados completo.

En SQL Server 2005, se considera que una consulta es recursiva cuando hace referencia a un CTE recursiva. La devolución de datos jerárquicos es un uso frecuente de las consultas recursivas; por ejemplo, mostrar los empleados en un organigrama o los datos en un escenario de lista de materiales en donde un producto primario tiene uno o varios componentes que, a su vez, tienen subcomponentes o son componentes de otros elementos primarios.

Una CTE recursiva puede simplificar en gran medida el código necesario para ejecutar una consulta recursiva en una instrucción SELECT, INSERT, UPDATE, DELETE o CREATE VIEW. En versiones anteriores de SQL Server, suele ser necesario que una consulta recursiva utilice tablas temporales, cursores y lógica para controlar el flujo de los pasos recursivos. Para obtener más información acerca de las expresiones de tabla comunes, vea Usar expresiones de tabla comunes.

Estructura de una CTE recursiva

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

Una CTE recursiva se compone de tres elementos:

Invocación de la rutina.
La primera invocación de la CTE recursiva se compone de una o varias CTE_query_definitions combinadas mediante operadores UNION ALL, UNION, EXCEPT o INTERSECT. Como estas definiciones de consulta forman el conjunto de resultados base de la estructura de CTE, se hace referencia a ellas como miembros delimitadores.
CTE_query_definitions se consideran miembros delimitadores a no ser que hagan referencia a la propia CTE. Todas las definiciones de consulta de miembro delimitador deben colocarse antes de la primera definición de miembro recursivo y debe utilizarse un operador UNION ALL para combinar el último miembro delimitador con el primer miembro recursivo.
Invocación recursiva de la rutina.
La invocación recursiva incluye una o varias CTE_query_definitions combinadas mediante operadores UNION ALL que hagan referencia a la propia CTE. Estas definiciones de consulta se conocen como miembros recursivos.
Comprobación de finalización.
La comprobación de finalización es implícita; la recursividad se detiene cuando no se devuelven filas desde la invocación anterior.

[!NOTA] Puede que una CTE recursiva compuesta incorrectamente provoque un bucle infinito. Por ejemplo, si la definición de 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, se puede limitar el número de niveles de recursividad permitidos para determinada instrucción mediante el uso de la sugerencia MAXRECURSION y un valor entre 0 y 32.767 en la cláusula OPTION de la instrucción INSERT, UPDATE, DELETE o SELECT. Para obtener más información, vea Query Hint (Transact-SQL) y WITH common_table_expression (Transact-SQL).

Pseudocódigo y semántica

La estructura de la CTE recursiva debe contener al menos un miembro delimitador y un miembro recursivo. El siguiente pseudocódigo muestra los componentes de una CTE recursiva sencilla que contiene un miembro delimitador único y un miembro recursivo único.

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:

Dividir la expresión CTE en miembros delimitadores y recursivos.
Ejecutar los miembros delimitadores para crear la primera invocación o conjunto de resultados base (T0).
Ejecutar los miembros recursivos con Ti como entrada y Ti+1 como salida.
Repetir el paso 3 hasta que se devuelva un conjunto vacío.
Se devuelve el conjunto de resultados. Es una instrucción UNION ALL de T0 a Tn.

Ejemplo

El siguiente ejemplo muestra la semántica de la estructura de CTE recursiva al devolver una lista jerárquica de empleados, que empieza con el empleado de rango superior, de la empresa Adventure Works Cycles. La instrucción que ejecuta la CTE limita el conjunto de resultados a los empleados del grupo de I+D. Una visita guiada de la ejecución del código sigue el ejemplo.

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

Visita guiada de código de ejemplo

La CTE recursiva, DirectReports, define un miembro delimitador y un miembro recursivo.
El miembro delimitador devuelve el conjunto de resultados base T0. Se trata del empleado de rango superior de la empresa; es decir, no es subordinado de ningún jefe.
El conjunto de resultados devuelto por el miembro delimitador es:
```
ManagerID EmployeeID Title                                   Level
--------- ---------- --------------------------------------- ------
NULL      109        Chief Executive Officer                 0
```
El miembro recursivo devuelve los subordinados directos del empleado en el conjunto de resultados del miembro delimitador. Esto se logra mediante una operación de combinación entre la tabla Employee y la CTE DirectReports. Es esta referencia a la propia CTE la que establece la invocación recursiva. En función del empleado que aparece como entrada en la CTE DirectReports (Ti), la combinación (Employee.ManagerID = DirectReports.EmployeeID) devuelve como salida (Ti+1), los empleados que tienen a (Ti) como jefe. Por lo tanto, la primera iteración del miembro recursivo devuelve este conjunto de resultados:
```
ManagerID EmployeeID Title                                   Level
--------- ---------- --------------------------------------- ------
109       12         Vice President of Engineering           1
```

El miembro recursivo se activa varias veces. La segunda iteración del miembro recursivo utiliza el conjunto de resultados de fila única del paso 3 (que contiene EmployeeID``12) como valor de entrada y devuelve este conjunto de resultados:

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

La tercera iteración del miembro recursivo utiliza el conjunto de resultados de fila única anterior (que contiene EmployeeID``3)) como valor de entrada y devuelve este conjunto de resultados:

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

La cuarta iteración del miembro recursivo utiliza el conjunto anterior de filas para los valores 4, 9, 11, 158, 263, 267 y 270 de EmployeeID como valor de entrada.
Este proceso se repite hasta que el miembro recursivo devuelve un conjunto de resultados vacío.

El conjunto de resultados final que devuelve la ejecución de la consulta es la unión de todos los conjuntos de resultados generados por los miembros delimitadores y recursivos.
El conjunto de resultados completo devuelto por el ejemplo es:

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)

Vea también

Consultas recursivas mediante expresiones de tabla comunes

Estructura de una CTE recursiva

Pseudocódigo y semántica

Ejemplo

Visita guiada de código de ejemplo

Vea también

Conceptos

Otros recursos

Ayuda e información

Recursos adicionales