Consultas recursivas mediante expresiones de tabla comunes
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.
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 no recursivos.
CTE_query_definitions se consideran miembros no recursivos a no ser que hagan referencia a la propia CTE. Todas las definiciones de consulta de miembro no recursivo deben colocarse antes de la primera definición de miembro recursivo y debe utilizarse un operador UNION ALL para combinar el último miembro no recursivo 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 Sugerencias de consulta (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 no recursivo y un miembro recursivo. El siguiente pseudocódigo muestra los componentes de una CTE recursiva sencilla que contiene un miembro no recursivo ú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 no recursivos 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 no recursivo y un miembro recursivo.
El miembro no recursivo 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 no recursivo es:
ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ NULL 109 Chief Executive Officer 0El miembro recursivo devuelve los subordinados directos del empleado en el conjunto de resultados del miembro no recursivo. 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 1El 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 EmployeeID12) como valor de entrada y devuelve este conjunto de resultados:
ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ 12 3 Engineering Manager 2La tercera iteración del miembro recursivo utiliza el conjunto de resultados de fila única anterior (que contiene EmployeeID3)) 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 3La 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)