SQL Server CTE и пример рекурсии

Ответ 1

Я не тестировал ваш код, просто попытался помочь вам понять, как он работает в комментарии;

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

Самый простой пример рекурсивного CTE, который я могу представить, чтобы проиллюстрировать его работу:

;WITH Numbers AS
(
    SELECT n = 1
    UNION ALL
    SELECT n + 1
    FROM Numbers
    WHERE n+1 <= 10
)
SELECT n
FROM Numbers

Q 1), как увеличивается значение N. если значение присваивается N каждый раз, тогда значение N может увеличиваться, но только первое значение N инициализируется.

A1: В этом случае N не является переменной. N является псевдонимом. Это эквивалентно SELECT 1 AS N. Это синтаксис личных предпочтений. В CTE в T-SQL есть 2 основных метода столбцов сложения. Я включил аналог простого CTE в Excel, чтобы попытаться проиллюстрировать более знакомым способом то, что происходит.

--  Outside
;WITH CTE (MyColName) AS
(
    SELECT 1
)
-- Inside
;WITH CTE AS
(
    SELECT 1 AS MyColName
    -- Or
    SELECT MyColName = 1  
    -- Etc...
)

Q 2) теперь здесь о CTE и рекурсии отношения сотрудников в тот момент, когда я добавляю двух менеджеров и добавляю еще несколько сотрудников под вторым менеджером, тогда проблема начинается. я хочу отобразить детали первого менеджера, а в следующих строках будут указаны только те детали сотрудников, которые подчиняются этому менеджеру

A2:

Отвечает ли этот код на ваш вопрос?

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

Еще один sql с древовидной структурой

SELECT ID,space(nLevel+
                    (CASE WHEN nLevel > 1 THEN nLevel ELSE 0 END)
                )+Name
FROM Hierarchy
ORDER BY Family, nLevel

Ответ 2

Хотелось бы наметить краткую семантическую параллель с уже правильным ответом.

В "простых" терминах рекурсивный CTE может быть семантически определен как следующие части:

1: запрос CTE. Также известен как ANCHOR.

2: Рекурсивный запрос CTE на CTE в (1) с UNION ALL (или UNION или EXCEPT или INTERSECT), поэтому возвращается окончательный результат.

3: Условие угла/завершения. Это по умолчанию, когда больше нет строк/кортежей, возвращаемых рекурсивным запросом.

Краткий пример, который сделает снимок прозрачным:

;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

Объяснение: Первый запрос CTE возвращает базовых поставщиков (например, листья), которые не поставляют никому другому поставщику напрямую (-1)

Рекурсивный запрос в первой итерации получает всех поставщиков, которые поставляют поставщикам, возвращенным ANCHOR. Этот процесс продолжается до тех пор, пока условие не вернет кортежи.

UNION ALL возвращает все кортежи по общим рекурсивным вызовам.

Еще один хороший пример можно найти здесь.

PS: Для работы рекурсивного CTE отношения должны иметь иерархическое (рекурсивное) условие для работы. Ex: elementId = elementParentId.. вы получаете точку.

Ответ 3

Процесс выполнения действительно путается с рекурсивным CTE, я нашел лучший ответ на https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx, и реферат процесса выполнения CTE как показано ниже.

Семантика рекурсивного выполнения выглядит следующим образом:

• Разделите выражение CTE на элементы привязки и рекурсии.
• Запустите анкерный элемент (ы), создающий первый набор или базовый результирующий набор (T0).
• Запустите рекурсивный элемент с Ti в качестве входа и Ti + 1 в качестве вывода.
• Повторите шаг 3 до тех пор, пока не будет возвращен пустой набор.
• Возвращает набор результатов. Это UNION ALL от T0 до Tn.

Ответ 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