主页 / dba / 问题 / 162298
Accepted
Paul
Asked: 2017-01-27 01:46:54 +0800 CST

递归 cte 避免循环

  • 772

我正在尝试编写递归 CTE 来探索工作流系统。不幸的是，由于循环，我得到了最大的递归错误：

with cteActs as
 (
  select a.id as [id], aa.TASKNEXTID as [childid]
  from TASK a
  inner join TASKNEXT aa on a.id = aa.TASKPARENTID
  where a.id != aa.TASKNEXTID
  ),
 cteNext as
(
    select a.*
    from cteActs as a
    where a.id=42
    union all
    select a.*
    from cteActs as a
    inner join cteNext as n on a.id = n.childid
    )
select * 
from cteNext

该表TASK是一个任务列表，例如其中 42 是“开始作业”。TASKNEXT将 42 链接到TASK表中可能的子任务。例如，它可以将 42 链接到 43，这可能是“查找材料”，例如

ID, name, childID
42, Start job, 43
42, Start job, 44
43, Find materials, 200
44, Report to boss, 201
201, Discuss with boss, 202
202, Receive payment, 44

我认为递归正在消亡，因为 44>201>202>44 创建了一个循环，查询不会从中逃脱。我怎么能允许这样做？我阅读的大多数示例/教程都假定严格的父>子关系，其中子永远不能成为其更高树中某些事物的父。

我想要得到的是一个不同的列表，TASKS该列表源自从任务 42 开始的流程，或者我选择的任何地方。

这是迭代 2，它可能有效，但运行速度很慢：

  select a.id as [id], aa.ACTIONACTIVITYID as [childid]
   into #temp
  from TASK a
  inner joinTASKNEXT aa on a.id = aa.TASKPARENTID
  where a.id != aa.TASKNEXTID    
  create clustered index [hello] on #temp (ID ASC)
  create nonclustered index [hello2] on #temp (childid ASC)
  ;
with cteNext as
(
    select a.*, 
    cast(',' + cast(a.ID as varchar(10)) + ',' as varchar(max)) as Path,
    0 as [cyc]
    from #temp as a
    where a.id=42
    union all
    select a.*,
    n.Path + cast(a.ID as varchar(10)) + ',',
         case when n.Path like '%,'+cast(a.ID as varchar(10))+',%' 
           then 1 
           else 0 
         end as [cyc]
    from #temp as a
    inner join cteNext as n on a.id = n.childid
    where n.cyc = 0
    )

select   id, childid
from cteNext
where cyc =0

执行计划

sql-server sql-server-2008-r2

1 个回答

  1. Best Answer

    因此，我设法提出的最佳结果是添加以下改进
    - 优化查询表（使用建议的索引创建新的临时表）
    - 添加路径元素以检查我没有重新访问路径的现有部分
    - 添加深度计数器作为限制器。然而，这确实意味着我有意识地选择不拥有完整的结果集

      select a.id as [id], aa.ACTIONACTIVITYID as [childid]
   into #temp
  from TASK a
  inner join TASKNEXT aa on a.id = aa.TASKPARENTID
  where a.id != aa.TASKNEXTID

  create clustered index [hello] on #temp (ID ASC)
  create nonclustered index [hello2] on #temp (childid ASC)
  ;
  drop table #temp2;
with cteNext as
(
    select a.*, 
    cast(',' + cast(a.ID as varchar(10)) + ',' as varchar(max)) as Path,
    0 as [cyc], 0 as [depth]
    from #temp as a
    where a.id=42
    union all
    select a.*,
    n.Path + cast(a.ID as varchar(10)) + ',',
         case when n.Path like '%,'+cast(a.ID as varchar(10))+',%' 
           then 1 
           else 0 
         end as [cyc], n.depth+1 as [depth]
    from #temp as a
    inner join cteNext as n on a.id = n.childid
    where n.cyc = 0 and n.depth <=11
    )

select *
into #temp2
from cteNext
where cyc =0

    这些数据的最初目的是“蓬松的”，我正在从中生成一个图表来显示我们的工作流程，因此至少在第一个实例中有限的深度是可以的。但如果有人有任何更好的答案

    • 3

相关问题