时间戳

首页 > 解决方案 > 排除自引用元素递归 cte

sql-server - 排除自引用元素递归 cte

问题描述

我正在创建一个递归 CTE，因为我需要创建一个组合层次。

当我FROM引用回TO. 然后它无限循环并达到最大递归限制。我可以在 Levels 上设置 where 子句，但是我最终可能不会得到正确的数据，因为我的树可以是 x 级深

我的数据可能看起来像这样

From    To      Total  Type
98579   10406   82     B
98579   17005   5834   S    
98579   18879   6323   S    
98579   18889   215    S
10406   43594   234    B
10406   73959   10     B
10406   98579   22824  B    
43594   83827   4      S
43594   38475   543    S

正如您在数据 98579 中看到的那样，命中 10406 并且反之亦然。

我的 cte 现在看起来像这样：

    ;WITH x AS
(
    -- anchor:
  SELECT b.[From]
      ,b.[To]
      ,b.[Total]
      ,b.[Type]
      ,0 as levels
  FROM [dbo].[Test] b

  where b.[FROM]= 98579
    UNION ALL
    -- recursive:
    SELECT tm.[FROM], tm.[TO], tm.[Total], tm.[Type],levels +1
    FROM  [dbo].[Test] AS tm INNER JOIN  x
    ON x.TO= tm.FROM
    --where levels <= 1
)
SELECT  *FROM x
order by levels

我能做些什么？

标签： sql-servertsqlrecursioncommon-table-expression

解决方案

如果我理解正确，您只想在递归回到开始的位置后终止递归。

一种选择是添加一个类似StartPoint或任何您想要调用它的列，然后在 where 子句中使用它来终止递归或将它们过滤掉。

在不具体知道您想要的输出是什么的情况下，我根据示例数据假设这是您所追求的，代码中添加了注释：

DECLARE @TestData TABLE
    (
        [From] INT
      , [To] INT
      , [Total] INT
      , [type] CHAR(1)
    );

INSERT INTO @TestData (
                          [From]
                        , [To]
                        , [Total]
                        , [type]
                      )
VALUES ( 98579, 10406, 82, 'B' ) , ( 98579, 17005, 5834, 'S' ) , ( 98579, 18879, 6323, 'S' ) , ( 98579, 18889, 215, 'S' ) , ( 10406, 43594, 234, 'B' ) , ( 10406, 73959, 10, 'B' ) , ( 10406, 98579, 22824, 'B' ) , ( 43594, 83827, 4, 'S' ) , ( 43594, 38475, 543, 'S' );

WITH [x]
AS (
   -- anchor:
   SELECT [b].[From] AS [StartPoint] --Where are we starting
        , [b].[From]
        , [b].[To]
        , [b].[Total]
        , [b].[type]
        , 0 AS [levels]
   FROM   @TestData [b]
   WHERE  [b].[From] = 98579
   UNION ALL
   -- recursive:
   SELECT     [x].[StartPoint] --Add it here
            , [tm].[From]
            , [tm].[To]
            , [tm].[Total]
            , [tm].[type]
            , [x].[levels] + 1
   FROM       @TestData AS [tm]
   INNER JOIN [x]
       ON [x].[To] = [tm].[From]
   WHERE      [x].[StartPoint] <> [tm].[From] --stop the recursion once we have come back to where it started, filter those out.
   )
SELECT   [x].[From]
        , [x].[To]
        , [x].[Total]
        , [x].[type]
        , [x].[levels]
FROM     [x]
ORDER BY [x].[levels];

给出结果：

From        To          Total       type levels
----------- ----------- ----------- ---- -----------
98579       10406       82          B    0
98579       17005       5834        S    0
98579       18879       6323        S    0
98579       18889       215         S    0
10406       43594       234         B    1
10406       73959       10          B    1
10406       98579       22824       B    1
43594       83827       4           S    2
43594       38475       543         S    2

在这个例子中，我包括了你添加过滤器的地方WHERE [b].[From] = 98579，如果那是为了显示循环引用的例子，或者你这样做是为了表明你的起点，这并不清楚。

如果您在上面的代码中删除 where 子句，它将给出所有内容。基本上每一行都被考虑StartPoint，你会得到每一行的所有重复，但是一旦它回到它开始的地方就会停止/过滤掉：

给你：

From        To          Total       type levels
----------- ----------- ----------- ---- -----------
98579       10406       82          B    0
98579       17005       5834        S    0
98579       18879       6323        S    0
98579       18889       215         S    0
10406       43594       234         B    0
10406       73959       10          B    0
10406       98579       22824       B    0
43594       83827       4           S    0
43594       38475       543         S    0
98579       10406       82          B    1
98579       17005       5834        S    1
98579       18879       6323        S    1
98579       18889       215         S    1
43594       83827       4           S    1
43594       38475       543         S    1
10406       43594       234         B    1
10406       73959       10          B    1
10406       98579       22824       B    1
43594       83827       4           S    2
43594       38475       543         S    2

推荐阅读