一个SQL SERVER递归查询实例

需求背景

有源数据如下所示：

要求根据源数据查询生成如下目标数据：

需求分析

由源数据表分析可知，该表通过[BOM母件编码]与[BOM子件]两列形成了上下级关系。

由目标数据与源数据表分析可知，BOM级次即为该件的层级，获取到层级值后，即可通过输入空格的方式生成第二列数据-层级编码，该列需要特别注意其排序问题，下面会有关于该排序的解决方法，版本即为当前BOM编码的版本，直接上级其实就是当前组件的母件编码，上级版本为母件版本，查找路径则可以通过递归查询的方式拼接起来。

表创建及数据准备

根据上述需求创建表：

CREATE TABLE [J](
    [BOM母件编码] [varchar](16) NULL,
    [母件版本] [varchar](8) NULL,
    [BOM子件] [varchar](16) NULL,
    [子件版本] [varchar](8) NULL
) 

录入示例数据：

INSERT [J] ([BOM母件编码], [母件版本], [BOM子件], [子件版本]) VALUES ('FT000034', 'V1.0', 'E001A06', 'V1.0')
INSERT [J] ([BOM母件编码], [母件版本], [BOM子件], [子件版本]) VALUES ('FT000034', 'V1.0', 'E003B09', 'V1.1')
INSERT [J] ([BOM母件编码], [母件版本], [BOM子件], [子件版本]) VALUES ('FT000038', 'V1.0', 'E003B03', 'V1.0')
INSERT [J] ([BOM母件编码], [母件版本], [BOM子件], [子件版本]) VALUES ('FT000038', 'V1.0', 'E001A06', 'V1.0')
INSERT [J] ([BOM母件编码], [母件版本], [BOM子件], [子件版本]) VALUES ('E003B03', 'V1.0', 'E003B09', 'V1.1')
INSERT [J] ([BOM母件编码], [母件版本], [BOM子件], [子件版本]) VALUES ('E001A06', 'V1.0', 'E003B09', 'V1.1')

数据实施

首先对源数据表格式进行一个整理，使之符合普通的递归查询格式，将BOM母件编码放到子件列，方便后面的查询，sql如下所示：

  select * into #t from  ( select BOM子件,[子件版本],[BOM母件编码],[母件版本] from J 
  union all 
  select distinct a.[BOM母件编码],a.[母件版本],null,null from J as a left join J as b on b.BOM子件=a.BOM母件编码
  where b.BOM母件编码 is null) as t;
select * from #t
drop table #t

执行结果如下所示：

可以看到这已经是一个标准的子父级关系的表。

SQL Server中的递归查询是通过CTE(表表达式)来实现。至少包含两个查询，第一个查询为定点成员，定点成员只是一个返回有效表的查询，用于递归的基础或定位点；第二个查询被称为递归成员，使该查询称为递归成员的是对CTE名称的递归引用是触发。在逻辑上可以将CTE名称的内部应用理解为前一个查询的结果集。

该处查询sql如下所示：

  --递归查询，完成级次、路径的划分
    with temp as(
    --定点成员
    select *,1 as [level],--层级定义为1
    cast(BOM子件+'('+子件版本+')' as varchar(256)) as [path], --定义查找路径，当前层级为其本身
    cast (row_number() over(order by BOM子件 asc) as varchar(32)) as r--定义行优先级，作为排序使用
    from #t where BOM母件编码 is null
  union all 
  --递归成员
  select  A.*,temp.[level]+1 as [level],--子件层级相对父级层级加一
  cast((A.BOM子件+'('+A.[子件版本]+')' +
  (case when temp.path<>'' then '->'+[path] else '' end))  as varchar(256))as [path],--定义查找路径，与父级查找路径拼一块儿
cast( r+'.'+cast( row_number() over(partition by A.BOM母件编码 order by A.BOM子件 asc) as varchar(32)) as varchar(32))as r--根据母件编码分组，排序，定义行优先级，作为排序使用字段
  from #t as A inner join temp  on A.BOM母件编码=temp.BOM子件
  )

在上述代码中，以[level]作为层级，在定点成员中，层级都是1，然后递归查询层级为其母级层级加一，解决了层级的查询问题。然后是查找路径，根据需求背景可知，定点成员列本身是不需要查找路径的，但由于子级的需要，在这里用其本身作为路径，在递归成员中，通过本级与父级的路径拼接形成查找路径。最后比较难的是排序问题，对于目标数据，没有任何一列或多列可以胜任排序的任务，因此直接在递归查询中生成一个行号来排序，定点成员直接根据BOM子件来排序生成行号，递归成员则通过母件编码之后生成行号，同时与父级行号拼接，从而生成可以为目标表排序使用的行号。

上述代码当中的多个cast转换，是由于拼接数据时导致定点成员与递归成员长度不一致，采用cast转换后生成一致数据类型。

完成上述查询后，中间数据存储到了temp临时表当中，最后完成查询的代码如下所示：

 
select [level] as BOM级次,
 space(([level]-1)*2)+BOM子件 as [BOM层级编码(缩进)],--根据级次输出缩进空格
 [子件版本] as 版本,ISNULL(BOM母件编码,'') as 直接上级,ISNULL(母件版本,'') as 上级版本,
 (case when [level]=1 then '' else [path] end) as 查找路径--根据目标数据格式，将层级为1的查找路径置空
 from temp order by r asc;

至此，已经实现了根据源数据生成目标表的任务，整体代码如下所示：

  
  ---没有提供数据表的名字，我用J作为源数据表的名字，本块儿的功能是将Bom子件与母件编码放到一张临时表当中，方便下面的递归查询
  select * into #t from  ( select BOM子件,[子件版本],[BOM母件编码],[母件版本] from J 
  union all 
  select distinct a.[BOM母件编码],a.[母件版本],null,null from J as a left join J as b on b.BOM子件=a.BOM母件编码
  where b.BOM母件编码 is null) as t;
  --递归查询，完成级次、路径的划分
    with temp as(
    --定点成员
    select *,1 as [level],
    cast(BOM子件+'('+子件版本+')' as varchar(256)) as [path], --定义查找路径
    cast (row_number() over(order by BOM子件 asc) as varchar(32)) as r--定义行优先级，作为排序使用
    from #t where BOM母件编码 is null
  union all 
  --递归成员
  select  A.*,temp.[level]+1 as [level],
  cast((A.BOM子件+'('+A.[子件版本]+')' +
  (case when temp.path<>'' then '->'+[path] else '' end))  as varchar(256))as [path],--定义查找路径，与父级查找路径拼一块儿
cast( r+'.'+cast( row_number() over(partition by A.BOM母件编码 order by A.BOM子件 asc) as varchar(32)) as varchar(32))as r--定义行优先级，作为排序使用字段
  from #t as A inner join temp  on A.BOM母件编码=temp.BOM子件
  )
 select [level] as BOM级次,
 space(([level]-1)*2)+BOM子件 as [BOM层级编码(缩进)],--根据级次输出缩进空格
 [子件版本] as 版本,ISNULL(BOM母件编码,'') as 直接上级,ISNULL(母件版本,'') as 上级版本,
 (case when [level]=1 then '' else [path] end) as 查找路径--根据目标数据格式，将层级为1的查找路径置空
 from temp order by r asc;
 --删除临时表
drop table #t

最后，有数据库相关的问题，欢迎找沐晨科技来解决，我们有专业的软件工程师队伍，可靠的售后服务为您解决问题。