在X递归循环后停止查询

Question

我有一个递归查询。查询适用于大多数的情况下，只有当有一个无限循环我希望得到一些结果从数据库返回的，而不是

最大递归100的前声明 完成已用完。

查询看起来像：

WITH bom ([id],[parentNumber],[warehouse],[sequenceNumber] 
        ,[childNumber],[childDescription],[qtyRequired],[childItemClass] 
        ,[childItemType],[scrapFactor],[bubbleNumber] ,[operationNumber] 
        ,[effectivityDate],[discontinuityDate],[companyID]) AS (
        select * from [products].[BillOfMaterial] where parentNumber IN ('XXXXXXXXXX') 
        and companyID = '0'UNION ALL 
        select c.* from bom b INNER JOIN [products].[BillOfMaterial] c on b.childNumber = c.parentNumber and c.companyID = '0') 
        SELECT distinct * FROM bom 

所以我想改变查询，这样我可以解决上述问题，并给出一些数据的水平恢复。通常树不会比5级更深。

这是可能的Sql服务器递归查询？

Answer 1

我不认为有一个OPTION做你想要什么，而是你可以创建一个变量来保存的要发生递归的最大次数，并在查询中引用它：

Declare @MaxLevel Int = 5; 

With bom (id, parentNumber, warehouse, sequenceNumber, childNumber, childDescription, qtyRequired, childItemClass, childItemType, scrapFactor, bubbleNumber, 
      operationNumber, effectivityDate, discontinuityDate, companyID, Level 
     ) 
As (Select *, 
      1 As Level 
    From products.BillOfMaterial 
    Where parentNumber In ('XXXXXXXXXX') 
      And companyID = '0' 
    Union All 
    Select c.*, 
      Level + 1 As Level 
    From bom b 
    Inner Join products.BillOfMaterial c 
     On b.childNumber = c.parentNumber 
      And c.companyID = '0' 
    Where Level < @MaxLevel 
    ) 
Select Distinct * 
From bom; 

这里有日期的工作示例：

Declare @MaxLevel Int = 5; 

;With Cte As 
(
    Select Convert(Date, GetDate()) As Date, 
      1 As Level 
    Union All 
    Select DateAdd(Day, 1, Date) As Date, 
      Level + 1 As Level 
    From Cte 
    Where Level < @MaxLevel 
) 
Select * 
From Cte 

结果

Date  Level 
2017-09-14 1 
2017-09-15 2 
2017-09-16 3 
2017-09-17 4 
2017-09-18 5 

Answer 2

类似下面应该这样做：

WITH bom ([id],[parentNumber],[warehouse],[sequenceNumber] 
        ,[childNumber],[childDescription],[qtyRequired],[childItemClass] 
        ,[childItemType],[scrapFactor],[bubbleNumber] ,[operationNumber] 
        ,[effectivityDate],[discontinuityDate],[companyID]) 
AS (
    select *, 1 as Depth 
    from [products].[BillOfMaterial] 
    where parentNumber IN ('XXXXXXXXXX') 
     and companyID = '0' 
    union all select c.*, b.Depth + 1 as Depth 
    from bom b 
     INNER JOIN [products].[BillOfMaterial] c 
     on b.childNumber = c.parentNumber 
     and c.companyID = '0' 
    --where b.Depth < xx 
    ) 
SELECT distinct * FROM bom 
    --where Depth < xx 

我没有测试过这种精确的代码，我不知道这where条款应被使用，但我以前做过查询这个样子。这个想法是在该轨道深度xx处放置一个任意计数器，并在到达该深度时关闭该查询。