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. Una visita guiada de la ejecución del código sigue el ejemplo.
-- Create an Employee table.
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)
);
-- Populate the table with values.
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
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 1 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 (MyEmployees.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 --------- ---------- ----------------------------- ------ 1 273 Vice President of Sales 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 EmployeeID273) 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 2La tercera iteración del miembro recursivo utiliza 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 3El 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 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