Basta ya de buscar en otros sitios porque estás al lugar necesario, poseemos la respuesta que buscas pero sin problemas.
Solución:
No he probado su código, solo intenté ayudarlo a comprender cómo funciona en el comentario;
WITH
cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)
AS
(
-->>>>>>>>>>Block 1>>>>>>>>>>>>>>>>>
-- In a rCTE, this block is called an [Anchor]
-- The query finds all root nodes as described by WHERE ManagerID IS NULL
SELECT EmployeeID, FirstName, LastName, ManagerID, 1
FROM Employees
WHERE ManagerID IS NULL
-->>>>>>>>>>Block 1>>>>>>>>>>>>>>>>>
UNION ALL
-->>>>>>>>>>Block 2>>>>>>>>>>>>>>>>>
-- This is the recursive expression of the rCTE
-- On the first "execution" it will query data in [Employees],
-- relative to the [Anchor] above.
-- This will produce a resultset, we will call it R1 and it is JOINed to [Employees]
-- as defined by the hierarchy
-- Subsequent "executions" of this block will reference Rn-1
SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,
r.EmpLevel + 1
FROM Employees e
INNER JOIN cteReports r
ON e.ManagerID = r.EmpID
-->>>>>>>>>>Block 2>>>>>>>>>>>>>>>>>
)
SELECT
FirstName + ' ' + LastName AS FullName,
EmpLevel,
(SELECT FirstName + ' ' + LastName FROM Employees
WHERE EmployeeID = cteReports.MgrID) AS Manager
FROM cteReports
ORDER BY EmpLevel, MgrID
El ejemplo más simple de un recursivo CTE Se me ocurre ilustrar su funcionamiento;
;WITH Numbers AS
(
SELECT n = 1
UNION ALL
SELECT n + 1
FROM Numbers
WHERE n+1 <= 10
)
SELECT n
FROM Numbers
Q 1) cómo se incrementa el valor de N. si el valor se asigna a N cada vez, entonces el valor N se puede incrementar, pero solo la primera vez que se inicializó el valor N.
A1: En este caso, N no es una variable. N es un alias. Es el equivalente de SELECT 1 AS N. Es una sintaxis de preferencia personal. Hay 2 métodos principales para asignar un alias a las columnas en un CTE en T-SQL. He incluido el análogo de un simple CTE en Excel para intentar ilustrar de una manera más familiar lo que está sucediendo.
-- Outside
;WITH CTE (MyColName) AS
(
SELECT 1
)
-- Inside
;WITH CTE AS
(
SELECT 1 AS MyColName
-- Or
SELECT MyColName = 1
-- Etc...
)
P 2) ahora aquí sobre CTE y la recursividad de la relación de empleado en el momento en que agrego dos gerentes y agrego algunos empleados más bajo el segundo gerente y luego comienza el problema. Quiero mostrar el primer detalle del gerente y en las siguientes filas solo los detalles de los empleados vendrán aquellos que están subordinados a ese gerente
A2:
¿Este código responde a su pregunta?
--------------------------------------------
-- Synthesise table with non-recursive CTE
--------------------------------------------
;WITH Employee (ID, Name, MgrID) AS
(
SELECT 1, 'Keith', NULL UNION ALL
SELECT 2, 'Josh', 1 UNION ALL
SELECT 3, 'Robin', 1 UNION ALL
SELECT 4, 'Raja', 2 UNION ALL
SELECT 5, 'Tridip', NULL UNION ALL
SELECT 6, 'Arijit', 5 UNION ALL
SELECT 7, 'Amit', 5 UNION ALL
SELECT 8, 'Dev', 6
)
--------------------------------------------
-- Recursive CTE - Chained to the above CTE
--------------------------------------------
,Hierarchy AS
(
-- Anchor
SELECT ID
,Name
,MgrID
,nLevel = 1
,Family = ROW_NUMBER() OVER (ORDER BY Name)
FROM Employee
WHERE MgrID IS NULL
UNION ALL
-- Recursive query
SELECT E.ID
,E.Name
,E.MgrID
,H.nLevel+1
,Family
FROM Employee E
JOIN Hierarchy H ON E.MgrID = H.ID
)
SELECT *
FROM Hierarchy
ORDER BY Family, nLevel
Otro sql con estructura de árbol.
SELECT ID,space(nLevel+
(CASE WHEN nLevel > 1 THEN nLevel ELSE 0 END)
)+Name
FROM Hierarchy
ORDER BY Family, nLevel
Me gustaría esbozar un breve paralelo semántico a una respuesta que ya es correcta.
En términos 'simples', un CTE recursivo se puede definir semánticamente como las siguientes partes:
1: la consulta CTE. También conocido como ANCLA.
2: La consulta CTE recursiva en el CTE en (1) con UNION ALL (o UNION o EXCEPT o INTERSECT) por lo que el resultado final se devuelve en consecuencia.
3: La condición de esquina / terminación. Que es por defecto cuando no hay más filas / tuplas devueltas por la consulta recursiva.
Un breve ejemplo que aclarará la imagen:
;WITH SupplierChain_CTE(supplier_id, supplier_name, supplies_to, level)
AS
(
SELECT S.supplier_id, S.supplier_name, S.supplies_to, 0 as level
FROM Supplier S
WHERE supplies_to = -1 -- Return the roots where a supplier supplies to no other supplier directly
UNION ALL
-- The recursive CTE query on the SupplierChain_CTE
SELECT S.supplier_id, S.supplier_name, S.supplies_to, level + 1
FROM Supplier S
INNER JOIN SupplierChain_CTE SC
ON S.supplies_to = SC.supplier_id
)
-- Use the CTE to get all suppliers in a supply chain with levels
SELECT * FROM SupplierChain_CTE
Explicación: La primera consulta CTE devuelve los proveedores base (como hojas) que no suministran a ningún otro proveedor directamente (-1)
La consulta recursiva en la primera iteración obtiene todos los proveedores que suministran a los proveedores devueltos por ANCLA. Este proceso continúa hasta que la condición devuelve tuplas.
UNION ALL devuelve todas las tuplas sobre el total de llamadas recursivas.
Otro buen ejemplo se puede encontrar aquí.
PD: Para que funcione un CTE recursivo, las relaciones deben tener una condición jerárquica (recursiva) para trabajar. Ej: elementId = elementParentId .. entiendes el punto.
El proceso de ejecución es realmente confuso con CTE recursivo, encontré la mejor respuesta en https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx y el resumen del proceso de ejecución de CTE es el siguiente.
La semántica de la ejecución recursiva es la siguiente:
- Divida la expresión CTE en miembros ancla y recursivos.
- Ejecute los miembros de anclaje creando la primera invocación o conjunto de resultados base (T0).
- Ejecute los miembros recursivos con Ti como entrada y Ti + 1 como salida.
- Repita el paso 3 hasta que se devuelva un juego vacío.
- Devuelve el conjunto de resultados. Esta es una UNION TODOS de T0 a Tn.
Agradecemos que quieras defender nuestro quehacer dejando un comentario y puntuándolo te damos las gracias.