sql-server - query - cte recursive sql server

Sql servidor CTE y ejemplo de recursión

Question

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:

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

Answer 1

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.

Answer 2

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

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.

Answer 3

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 sintaxis 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...
)

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

Answer 4

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