Sql server CTE和递归示例

4 回答

• 9

  我没有测试过您的代码，只是试图帮助您了解它在评论中的运作方式;

  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) how value of N is getting incremented. if value is assign to N every time then N value can be incremented but only first time N value was initialize .

  A1: 在这种情况下， N 不是变量 . N 是别名 . 它相当于 SELECT 1 AS N . 这是个人偏好的语法 . 在 T-SQL 中 CTE 中有两种别名列的方法 . 我在 Excel 中包含了一个简单的 CTE 模拟，试图以更熟悉的方式说明发生了什么 .

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

  

  Q 2) now here about CTE and recursion of employee relation the moment i add two manager and add few more employee under second manager then problem start. i want to display first manager detail and in the next rows only those employee details will come those who are subordinate of that manager

  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
  

  回复于 2024-04-18T23:11:19+08:00
• -2

  想概述一个与已经正确的答案平行的简短语义 .

  在“简单”术语中，递归CTE可以在语义上定义为以下部分：

  1：CTE查询 . 也称为ANCHOR .

  2：在（1）中使用UNION ALL（或UNION或EXCEPT或INTERSECT）对CTE进行递归CTE查询，从而返回最终结果 .

  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在总递归调用中返回所有元组 .

  另一个很好的例子可以找到here .

  PS：要使递归CTE起作用，关系必须具有分层（递归）条件才能工作 . 例如：elementId = elementParentId ..你明白了 .

  回复于 2024-04-18T23:11:19+08:00
• 6

  执行过程真的与递归CTE混淆，我在https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx找到了最佳答案，CTE执行过程的摘要如下 .

  递归执行的语义如下：

  • 将CTE表达式拆分为锚点和递归成员 .

  • 运行锚定成员创建第一个调用或基本结果集（T0） .

  • 运行递归成员，其中Ti作为输入，Ti 1作为输出 .

  • 重复步骤3，直到返回空集 .

  • 返回结果集 . 这是T0到Tn的UNION ALL .

  回复于 2024-04-18T23:11:19+08:00
• 173

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

  回复于 2024-04-18T23:11:19+08:00