SQLServer数据库里的递归CTE详细说明

 

SQLServer数据库里的递归CTE详细说明

 

 

用实例来说明：

样例： 

-- 解释CTE递归的运算逻辑（代码不一定可用,但逻辑准确）
WITH BOM_CTE AS (

    -- 基础层(B段)：选择特定BOM物料编码的所有BOM条目，并设置层级为1
    SELECT BOMNO AS 'TopBOM', COMPID, REQQTY
           , 1 AS Level, BOMNO AS 'ParentID' 
    FROM [BOM] B
    WHERE  BOMNO = 'ABC00001'

    UNION ALL

    -- 递归层(d段)：展开下一层BOM，并增加层级
    SELECT 'ABC00001' AS 'TopBOM', d.COMPID,(d.REQQTY * cte.REQQTY) AS REQQTY
           ,cte.Level + 1, d.BOMNO AS 'ParentID' 
    FROM [BOM] d
    INNER JOIN BOM_CTE cte ON d.BOMNO = cte.COMPID    --递归层只允许内连接  

)
-- 最终选择(CTE查询段)：只选择最底层物料，并包括层级信息
SELECT TopBOM AS '顶层BOM',Level, COMPID AS '组件物料', REQQTY AS'组件用量',ParentID AS'组件的上层物料'
FROM BOM_CTE
WHERE COMPID NOT IN (select BOMNO from [BOM])
OPTION (MAXRECURSION 10);   -- 根据业务情况递归最大次数不会超过10次，超过的直接忽略。 

                            --【值是0时，则允许无限递归，以防BOM结构深度未知】

 

/*  
    假设 [BOM] 表的结构是 BOMNO、COMPID、REQQTY； 
    其中 BOMNO是COMPID的直接上层物料（而不是下层物料），REQQTY是COMPID在该BOMNO里的单位用量。
    --用某个产品物料编码 ABC00001 来做测试。
*/

　　

样例讲解： 

在SQL Server中，递归公共表表达式（CTE）的执行顺序遵循以下步骤：

1. **基础层（B段）**：
- 递归的起点是基础层，这是递归CTE的第一部分。在您的情况下，基础层是选择特定BOM物料编码`ABC00001`的所有BOM条目，并设置层级为1。
- 这个查询首先执行，返回所有顶层BOM条目，这些条目将作为递归的起点和基础，所以叫基础层。
- 在整个递归查询里，这个查询只会执行一次！

2. **第一次递归层（d段）**：
- 第一次递归层的执行将基础层的结果作为输入。
 它将从`[BOM]`表中选择那些`BOMNO`与基础层返回的`COMPID`相匹配的条目，并计算层级为2。
- 这一步将返回第一层子组件及其对应的层级。
- 这一步的参数说明：cte 代表的是基础层

3. **第二次递归层（d段）**：
- 第二次递归层的执行将第一次递归层的结果作为输入。
  它将再次从`[BOM]`表中选择那些`BOMNO`与第一次递归层返回的`COMPID`相匹配的条目，并计算层级为3。
- 这一步将返回第二层子组件及其对应的层级。
- 这一步的参数说明：cte 代表的是第一次递归层

4. **第三次递归层（d段）**：
- 第三次递归层的执行将第二次递归层的结果作为输入。
它将从`[BOM]`表中选择那些`BOMNO`与第二次递归层返回的`COMPID`相匹配的条目，并计算层级为4。
- 这一步将返回第三层子组件及其对应的层级。
- 这一步的参数说明：cte 代表的是第二次递归层

5. **后续递归层（d段）**：
- 递归层将继续执行，直到没有更多的子组件可以匹配，即`COMPID`不再作为任何其他条目的`BOMNO`出现。
- 第N次递归层的执行将第N-1次递归层的结果作为输入。
   它将从`[BOM]`表中选择那些`BOMNO`与 上一次递归层返回的`COMPID`相匹配的条目，并将计算层级+1。
   这一步的参数说明：cte 代表的是上一次递归层
....
- 在整个递归查询里，d段的查询会不断循环直至无法再次展开！

 

6. **最终选择（CTE查询段）**：
- 一旦所有的递归层都执行完毕，最终选择将从整个递归CTE（包括基础层和所有递归层）中选择最底层物料。
- 这个查询将检查每个`COMPID`是否不再作为任何其他BOM条目的`BOMNO`出现，如果是，则认为它是最底层物料，并返回这些物料及其层级。

递归CTE的执行顺序是从基础层开始，逐层递归，直到无法继续递归为止，然后执行最终选择来返回结果。每次递归层的执行都是基于前一次递归层的结果。这个过程会持续进行，直到所有可能的递归层都被处理完毕。

 

·