遞歸 cte 避免循環

我正在嘗試編寫遞歸 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

執行計劃

因此，我設法提出的最佳結果是添加以下改進

• 優化查詢表（使用建議的索引創建新的臨時表）

• 添加路徑元素以檢查我沒有重新訪問路徑的現有部分

• 添加深度計數器作為限制器。然而，這確實意味著我有意識地選擇不擁有完整的結果集

 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

這些數據的最初目的是“蓬鬆的”，我正在從中生成一個圖表來顯示我們的工作流程，因此至少在第一個實例中有限的深度是可以的。但如果有人有任何更好的答案

引用自：https://dba.stackexchange.com/questions/162298