SQL Server CTE 多层递归查询、展开工单bom语法

SQL Server提供CTE（Common Table Expression），递归查询主要用于层次结构的查询，从叶级（Leaf Level）向顶层（Root Level）查询，或从顶层向叶级查询，或递归的路径（Path）

可广泛用在展工单bom的语句中， 不过个人感觉没有 oracle 的强大， 具体oracle的 请看我的另一篇文章。

下面显示下 sql server 上的使用方法：

1，递归查询至少包含两个子查询：

• 第一个子查询称作定点（Anchor）子查询：定点查询只是一个返回有效表的查询，用于设置递归的初始值；
• 第二个子查询称作递归子查询：该子查询调用CTE名称，触发递归查询，实际上是递归子查询调用递归子查询；
• 两个子查询使用union all，求并集；

2，CTE的递归终止条件

递归查询没有显式的递归终止条件，只有当递归子查询返回空结果集（没有数据行返回）或是超出了递归次数的最大限制时，才停止递归。

默认的递归查询次数是100，可以使用查询提示（hint）：MAXRECURSION 控制递归的最大次数：OPTION( MAXRECURSION 16)；如果允许无限制的递归次数，使用查询提示：option(maxrecursion 0)；当递归查询达到指定或默认的 MAXRECURSION 数量限制时，SQL Server将结束查询并返回错误，如下：

The statement terminated. The maximum recursion 10 has been exhausted before statement completion.

事务执行失败，该事务包含的所有操作都被回滚。在产品环境中，慎用maxrecursion 查询提示，推荐通过 where 条件限制递归的次数。

比如下方例子， 最后加入  option 会报错：  select * from temp OPTION (MAXRECURSION 3)

3，递归步骤

step1：定点子查询设置CTE的初始值，即CTE的初始值Set0；

递归调用的子查询过程：递归子查询调用递归子查询；

step2：递归子查询第一次调用CTE名称，CTE名称是指CTE的初始值Set0，第一次执行递归子查询之后，CTE名称是指结果集Set1；

step3：递归子查询第二次调用CTE名称，CTE名称是指Set1，第二次执行递归子查询之后，CTE名称是指结果集Set2；

step4：在第N次执行递归子查询时，CTE名称是指Set(N-1)，递归子查询都引用前一个递归子查询的结果集；

Step5：如果递归子查询返回空数据行，或超出递归次数的最大限制，停止递归；

具体先创建好测试表内表内数据：

--创建组织架构表

create table Org(
    ParentId nvarchar(50),
    OrgId nvarchar(50) primary key,
    OrgName nvarchar(500),
	Seq int
)
 --添加数据
insert into Org (ParentId,OrgId,OrgName,seq) values(null,'00','总集团',0)
insert into Org (ParentId,OrgId,OrgName,seq) values('00','01','A区',1)
insert into Org (ParentId,OrgId,OrgName,seq) values('00','02','B区',2)

insert into Org (ParentId,OrgId,OrgName,seq) values('01','0101','A区子公司',1)
insert into Org (ParentId,OrgId,OrgName,seq) values('02','0201','B区子公司',1)

insert into Org (ParentId,OrgId,OrgName,seq) values('0101','010101','A区子公司商务部1',1)
insert into Org (ParentId,OrgId,OrgName,seq) values('0101','010102','A区子公司商务部2',2)
insert into Org (ParentId,OrgId,OrgName,seq) values('0101','010103','A区子公司商务部3',3)

insert into Org (ParentId,OrgId,OrgName,seq) values('0201','020101','B区子公司财务部1',1)
insert into Org (ParentId,OrgId,OrgName,seq) values('0201','020102','B区子公司财务部2',2)

select * from Org

创建表后表的内容如下，后续就以此测试

sql 实现递归方法1：

--向下递归
with temp (Ancestor,ParentName, ParentId,OrgId,OrgName,OriginalSeq,Seq,level)
as
(
select org.OrgName AS Ancestor,org.OrgName ,ParentId,OrgId,OrgName,Seq as OriginalSeq, CAST('00' AS NVARCHAR(50)) as Seq ,OrgName  from Org
where OrgId='00'
union all
select temp.Ancestor, temp.OrgName, a.ParentId, a.OrgId,a.OrgName ,a.Seq as OriginalSeq,
		CAST(temp.Seq + '.' + RIGHT('00'+ CAST(a.seq AS NVARCHAR(50) ),2) AS NVARCHAR(50)) ,
		CAST(temp.level + '/' + a.OrgName AS NVARCHAR(500)) 
			from Org a
inner join temp on a.ParentId = temp.OrgId
)
select * from temp 

得到结果：

sql 实现递归方法2：多一层嵌套，感觉这种语法更灵活，多了一个 bom 这个临时表嵌套，不过要先确定 temp 中需要定义好的字段才行

;
WITH 
temp as  (select Org.*,'' Level from Org),
        bom
          AS ( SELECT   temp.OrgName AS Ancestor ,temp.OrgName as ParentName,
temp.ParentId ,temp.OrgId ,temp.OrgName , temp.Seq as OriginalSeq,
   CAST('00' AS NVARCHAR(50)) AS Pseq , temp.OrgName as Level
               FROM     temp 
               WHERE    temp.ParentId IS NULL   
               UNION ALL
               SELECT   p.Ancestor,p.OrgName ,c.ParentId ,c.OrgId ,c.OrgName ,c.Seq as OriginalSeq,
                        CAST(p.pseq + '.' + RIGHT('00'+ CAST(c.seq AS NVARCHAR(50)),2) AS NVARCHAR(50))
						,CAST(p.Level + '/' + c.OrgName AS NVARCHAR(500)) 
               FROM     temp AS c
                        JOIN bom AS p ON p.OrgId = c.ParentId

             )
	 SELECT * FROM bom  

结果：

综上可以看出，只要又 父-子 行这样的对应关系，用这种递归sql，就可以一层层的往下展开，显示从跟节点到最后子节点的关系，很方便

oracle 数据库上的语法，请参考这篇文章：

oracle-EBS-PL/sql 工单BOM产品领料批次追溯, 成品如何倒推追溯到原材料/底层物料 SQL START WITH CONNECT BY prior