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