Sql servidor CTE y ejemplo de recursión

Nunca uso CTE con recursividad. Estaba leyendo un artículo sobre eso. Este artículo muestra información del empleado con la ayuda del servidor Sql CTE y recursión. Básicamente muestra los empleados y su información de gerente. No puedo entender cómo funciona esta consulta. Aquí está la consulta:

WITH cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel) AS ( SELECT EmployeeID, FirstName, LastName, ManagerID, 1 FROM Employees WHERE ManagerID IS NULL UNION ALL SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID, r.EmpLevel + 1 FROM Employees e INNER JOIN cteReports r ON e.ManagerID = r.EmpID ) SELECT FirstName + ' ' + LastName AS FullName, EmpLevel, (SELECT FirstName + ' ' + LastName FROM Employees WHERE EmployeeID = cteReports.MgrID) AS Manager FROM cteReports ORDER BY EmpLevel, MgrID 

Aquí estoy publicando cómo se muestra la salida: enter image description here

Solo necesito saber cómo está mostrando al gerente primero y luego a su subordinado en un bucle. Supongo que el primer enunciado sql solo se activa una vez y devuelve todos los ID de empleado.

Y la segunda consulta se activa repetidamente, consultando la base de datos en la que el empleado existe con la identificación del administrador actual.

Explique cómo se ejecuta la instrucción sql en un ciclo interno y también dígame el orden de ejecución de sql. Gracias.

MI segunda fase de la pregunta

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

Q 2) CTE y recursión de las relaciones con los empleados:

En el momento en que agrego dos gerentes y agrego algunos empleados más bajo el segundo gerente, es donde comienza el problema.

Quiero mostrar el primer detalle del administrador y en las siguientes filas solo los detalles del empleado que se relacionan con el subordinado de ese gerente.

Suponer

 ID Name MgrID Level --- ---- ------ ----- 1 Keith NULL 1 2 Josh 1 2 3 Robin 1 2 4 Raja 2 3 5 Tridip NULL 1 6 Arijit 5 2 7 Amit 5 2 8 Dev 6 3 

Quiero mostrar los resultados de esa manera con expresiones CTE. Por favor, dígame qué modificar en mi sql, que aquí proporcioné para establecer relaciones entre el gerente y el empleado. Gracias.

Quiero que la salida sea así:

 ID Name MgrID nLevel Family ----------- ------ ----------- ----------- -------------------- 1 Keith NULL 1 1 3 Robin 1 2 1 2 Josh 1 2 1 4 Raja 2 3 1 5 Tridip NULL 1 2 7 Amit 5 2 2 6 Arijit 5 2 2 8 Dev 6 3 2 

Es posible…?

No he probado tu código, solo intenté ayudarte a comprender cómo funciona en los comentarios;

 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 R{1} and it is JOINed to [Employees] -- as defined by the hierarchy -- Subsequent "executions" of this block will reference R{n-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 CTE recursivo que puedo imaginar para ilustrar su operación es;

 ;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 puede incrementarse, pero solo se inicializa el valor N de la primera vez .

A1: en este caso, N no es una variable. N es un alias. Es el equivalente de SELECT 1 AS N Es una syntax de preferencia personal. Hay 2 métodos principales para aliasar columnas en un CTE en T-SQL . He incluido el análogo de un CTE simple en Excel para tratar de 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... ) 

Excel_CTE

Q 2) ahora aquí sobre CTE y la recursión de la relación con el empleado en el momento en que agrego dos gerentes y agrego algunos empleados más bajo el segundo gerente y luego comienzo el problema. Quiero mostrar los detalles del primer gerente y en las siguientes filas solo aparecerán los detalles de los empleados quienes estén subordinados a ese gerente

A2:

¿Responde este código a tu 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 delinear un breve paralelo semántico a una respuesta ya 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 ANCHOR.

2: La consulta CTE recursiva en el CTE en (1) con UNION ALL (o UNION o EXCEPT o INTERSECT) para que se devuelva el resultado final.

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 ANCHOR. Este proceso continúa hasta que la condición devuelva tuplas.

UNION ALL devuelve todas las tuplas sobre el total de llamadas recursivas.

Otro buen ejemplo se puede encontrar aquí .

PD: Para que un CTE recursivo funcione, 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 como a continuación.

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

  1. Divida la expresión CTE en miembros de anclaje y recursivos.
  2. Ejecute los miembros de anclaje que crean la primera invocación o el 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. Este es un UNION TODO de T0 a Tn.
  --DROP TABLE #Employee CREATE TABLE #Employee(EmpId BIGINT IDENTITY,EmpName VARCHAR(25),Designation VARCHAR(25),ManagerID BIGINT) INSERT INTO #Employee VALUES('M11M','Manager',NULL) INSERT INTO #Employee VALUES('P11P','Manager',NULL) INSERT INTO #Employee VALUES('AA','Clerk',1) INSERT INTO #Employee VALUES('AB','Assistant',1) INSERT INTO #Employee VALUES('ZC','Supervisor',2) INSERT INTO #Employee VALUES('ZD','Security',2) SELECT * FROM #Employee (NOLOCK) ; WITH Emp_CTE AS ( SELECT EmpId,EmpName,Designation, ManagerID ,CASE WHEN ManagerID IS NULL THEN EmpId ELSE ManagerID END ManagerID_N FROM #Employee ) select EmpId,EmpName,Designation, ManagerID FROM Emp_CTE order BY ManagerID_N, EmpId