[sql-server] SQL Server CTE 및 재귀 예제

나는 당신의 코드를 테스트하지 않았고, 그것이 주석에서 어떻게 작동하는지 이해하도록 돕기 위해 노력했습니다.

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합니다. 개인 취향의 구문입니다. A의 열을 앨리어싱의 2 가지 주요 방법이 있습니다 CTE에가 T-SQL. 나는 간단한의 아날로그 포함 시켰 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

답변

;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

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