sqlserver 递归

需求：树状结构的产品BOM图，每个节点表示一个物料，需要输出物料层级间的关系。

 效果如下：

1、构建SQLserver中可以递归的结果集语句；

select a.wo_wlid,b.wobom_wlid from jserp.wo a,jserp.wobom b
where a.wo_zlh='60585480_50' and a.wo_woid=b.wobom_woid

2、将上面的结果集作为数据源进行递归；

with cte as(
select a.wo_woid,a.wo_zlh,a.wo_wlid,b.wobom_wlid,cast(rtrim(a.wo_wlid) as varchar(max)) as WLPath from jserp.wo a,jserp.wobom b
where a.wo_zlh='60585480_50' and a.wo_woid=b.wobom_woid and a.wo_wlid='110102000581' 
union all
select a.wo_woid,a.wo_zlh,a.wo_wlid,a.wobom_wlid,cte.WLPath+'->'+rtrim(a.wo_wlid) from 
(select a.wo_woid,a.wo_zlh,a.wo_wlid,b.wobom_wlid from jserp.wo a,jserp.wobom b
where a.wo_zlh='60585480_50' and a.wo_woid=b.wobom_woid) a
inner join cte  on cte.wobom_wlid=a.wo_wlid
)
select WLPath+'->'+rtrim(wobom_wlid) as WLPath from cte where wo_wlid<>'110102000581' 

下面示例中，注释很好地解释了递归SQL查询的逻辑和每个部分的作用

--"with cte as(...)"：这是公共表表达式的开始。在这个部分，我们定义了一个名为 "cte" 的临时结果集，它由两个部分组成：一个基础查询和一个递归查询。
with cte as
(
--基础查询：作为递归查询的起始数据（层级结构的最外层）
select a.wo_woid,a.wo_zlh,a.wo_wlid,b.wobom_wlid,a.u_wo_faswlid,b.u_wobom_faswlid from jserp.wo a,jserp.wobom b
where a.wo_zlh='1137313678/220' and a.wo_woid=b.wobom_woid
and a.u_wo_faswlid='110101000613-000297_4551579306_00010_00'
union all
--递归查询：从基础查询的结果集中选择数据， 一直执行递归查询，直到没有新的行可以添加到结果集为止。（这是递归查询的特性，它可以处理层级结构的数据。）
--基础查询的结果集：union all和inner join 之间的select查询
select a.wo_woid,a.wo_zlh,a.wo_wlid,a.wobom_wlid,a.u_wo_faswlid,a.u_wobom_faswlid from(
select a.wo_woid,a.wo_zlh,a.wo_wlid,b.wobom_wlid,a.u_wo_faswlid,b.u_wobom_faswlid  from jserp.wo a,jserp.wobom b
where a.wo_zlh='1137313678/220' and a.wo_woid=b.wobom_woid) a
--cte.u_wobom_faswlid 是外部查询的字段，a.u_wo_faswlid 是内部查询的字段。在递归查询中，我们使用这两个字段来建立递归关系。
inner join cte on cte.u_wobom_faswlid=a.u_wo_faswlid
)
--这是主查询
select  distinct wo_woid,u_wo_faswlid from cte

3、sqlserver递归注意点：
　　1.union all可以重复查询,union有distinct作用,会自动去除重复行,用在递归语句中，本身会报错，就算不报错也不会继续向下递归，必须使用union all；
　　2.CTE（Common Table Expression，公用表达式）是一种临时命名结果集,在递归语句中，作为主表使用，cte指向子节点的物料ID和从表节点的物料ID建立关联；
　　3.注意可能存在的报错
　　　　报错：在递归查询 "cte" 的列 "WLPath" 中，定位点类型和递归部分的类型不匹配。
　　　　原因：由于字段 wo_wlid 递归循环，而表wo中的wo_wlid字段长度不够，所以必须将wo_wlid字符串长度设置为max或8000。
　　4.受限于sqlserver自身，WLPath字段长度要小于8000，不能无限递归。

 实用级代码

USE [NEWDBERP_Test2]
GO
/****** Object:  StoredProcedure [dbo].[SJ_GetWLPath]    Script Date: 2024-3-5 10:16:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SJ_GetWLPath](@wo_zlh varchar(100),@sub_wlid varchar(20))
as
begin 

with cte as(
select a.wo_woid,a.wo_zlh,a.wo_wlid,b.wobom_wlid,a.u_wo_faswlid,b.u_wobom_faswlid,
cast(rtrim(a.wo_wlid) as varchar(max)) as WLPath,
cast(rtrim(a.u_wo_faswlid) as varchar(max)) as Fas_WLPath,
cast(rtrim(a.wo_woid) as varchar(max)) as MOPath from jserp.wo a,jserp.wobom b
where a.wo_zlh like @wo_zlh+'%' and a.wo_woid=b.wobom_woid 
and a.wo_wlid in
    (
     select a.wo_wlid from jserp.wo a,jserp.wobom b
    where a.wo_zlh like @wo_zlh+'%' and a.wo_woid=b.wobom_woid
    except
    select b.wobom_wlid from jserp.wo a,jserp.wobom b
    where a.wo_zlh like @wo_zlh+'%' and a.wo_woid=b.wobom_woid
    ) 
union all
select a.wo_woid,a.wo_zlh,a.wo_wlid,a.wobom_wlid,a.u_wo_faswlid,a.u_wobom_faswlid,
cte.WLPath+'->'+rtrim(a.wo_wlid),
cte.Fas_WLPath+'->'+rtrim(a.u_wo_faswlid),
cte.MOPath+'->'+rtrim(a.wo_woid) from 
(select a.wo_woid,a.wo_zlh,a.wo_wlid,b.wobom_wlid,a.u_wo_faswlid,b.u_wobom_faswlid from jserp.wo a,jserp.wobom b
where a.wo_zlh like @wo_zlh+'%' and a.wo_woid=b.wobom_woid) a
inner join cte  on cte.u_wobom_faswlid=a.u_wo_faswlid
)

select wo_zlh,MOPath,WLPath+'->'+rtrim(wobom_wlid) WLPath,Fas_WLPath+'->'+rtrim(u_wobom_faswlid) Fas_WLPath from cte  where wobom_wlid=@sub_wlid

end