您没有具体指明方言或座标,因此很难树立完美的榜样。

-- Some random data
IF OBJECT_ID( tempdb..#MyTable ) IS NOT NULL
    DROP TABLE #MyTable

CREATE TABLE #MyTable (ID INT PRIMARY KEY, ParentID INT NULL, Description VARCHAR(100))
INSERT INTO #MyTable (ID, ParentID, Description) VALUES
(1, NULL,  Parent ), -- Try changing the second value (NULL) to 1 or 2 or 3
(2, 1,  Child ), -- Try changing the second value (1) to 2 
(3, 2,  SubChild )
-- End random data

;WITH RecursiveCTE (StartingID, Level, Parents, Loop, ID, ParentID, Description) AS
(
    SELECT ID, 1,  |  + CAST(ID AS VARCHAR(MAX)) +  | , 0, * FROM #MyTable
    UNION ALL
    SELECT R.StartingID, R.Level + 1, 
        R.Parents + CAST(MT.ID AS VARCHAR(MAX)) +  | ,
        CASE WHEN R.Parents LIKE  %|  + CAST(MT.ID AS VARCHAR(MAX)) +  |%  THEN 1 ELSE 0 END,
        MT.*
        FROM #MyTable MT
        INNER JOIN RecursiveCTE R ON R.ParentID = MT.ID AND R.Loop = 0
)

SELECT StartingID, Level, Parents, MAX(Loop) OVER (PARTITION BY StartingID) Loop, ID, ParentID, Description 
    FROM RecursiveCTE 
    ORDER BY StartingID, Level

如果有的话,在休养的牛群中有 lo,就会出现这种情况。 参看<代码>Loop。 根据目前的数据,没有漏洞。 在评论中,有实例说明如何改变价值观,以造成 lo。

最终,休研中心以VARCHAR(TM)的形式建立了一套:,>code> ids in the form>>>>。 (所谓的<代码>Parents),然后对现行<代码>ID已经列入“名单”进行检查。 如果是,它就将<代码>Loop栏至1。 该栏在复读条目(ABD R.Loop = 0)上作了核对。

The ending query uses a MAX() OVER (PARTITION BY ...) to set to 1 the Loop column for a whole "block" of chains.

A little more complex, that generates a "better" report:

-- Some random data
IF OBJECT_ID( tempdb..#MyTable ) IS NOT NULL
    DROP TABLE #MyTable

CREATE TABLE #MyTable (ID INT PRIMARY KEY, ParentID INT NULL, Description VARCHAR(100))
INSERT INTO #MyTable (ID, ParentID, Description) VALUES
(1, NULL,  Parent ), -- Try changing the second value (NULL) to 1 or 2 or 3
(2, 1,  Child ), -- Try changing the second value (1) to 2 
(3, 3,  SubChild )
-- End random data

-- The "terminal" childrens (that are elements that don t have childrens
-- connected to them)
;WITH WithoutChildren AS
(
    SELECT MT1.* FROM #MyTable MT1
        WHERE NOT EXISTS (SELECT 1 FROM #MyTable MT2 WHERE MT1.ID != MT2.ID AND MT1.ID = MT2.ParentID)
)

, RecursiveCTE (StartingID, Level, Parents, Descriptions, Loop, ParentID) AS
(
    SELECT ID, -- StartingID 
        1, -- Level
         |  + CAST(ID AS VARCHAR(MAX)) +  | , 
         |  + CAST(Description AS VARCHAR(MAX)) +  | , 
        0, -- Loop
        ParentID
        FROM WithoutChildren
    UNION ALL
    SELECT R.StartingID, -- StartingID
        R.Level + 1, -- Level
        R.Parents + CAST(MT.ID AS VARCHAR(MAX)) +  | ,
        R.Descriptions + CAST(MT.Description AS VARCHAR(MAX)) +  | , 
        CASE WHEN R.Parents LIKE  %|  + CAST(MT.ID AS VARCHAR(MAX)) +  |%  THEN 1 ELSE 0 END,
        MT.ParentID
        FROM #MyTable MT
        INNER JOIN RecursiveCTE R ON R.ParentID = MT.ID AND R.Loop = 0
)

SELECT * FROM RecursiveCTE 
    WHERE ParentID IS NULL OR Loop = 1

这种询问应归还所有“最后儿童”的牢房,并重归母链。 <代码>Loop 如果没有漏洞,1 如果存在漏洞。

邮局非常容易通过收集阵列中所有访问过的节点来防止这种情况。

设置:

create table hierarchy (id integer, parent_id integer);

insert into hierarchy
values
(1, null), -- root element
(2, 1), -- first child
(3, 1), -- second child
(4, 3), 
(5, 4), 
(3, 5); -- endless loop

退学:

with recursive tree as (
  select id, 
         parent_id, 
         array[id] as all_parents
  from hierarchy
  where parent_id is null
  
  union all
  
  select c.id, 
         c.parent_id,
         p.all_parents||c.id
  from hierarchy c
     join tree p
      on c.parent_id = p.id 
     and c.id <> ALL (p.all_parents) -- this is the trick to exclude the endless loops
)
select *
from tree;

为了同时为多 trees树做这项工作,你需要把根 no子传给儿童:

with recursive tree as (
  select id, 
         parent_id, 
         array[id] as all_parents, 
         id as root_id
  from hierarchy
  where parent_id is null
  
  union all
  
  select c.id, 
         c.parent_id,
         p.all_parents||c.id, 
         p.root_id
  from hierarchy c
     join tree p
      on c.parent_id = p.id 
     and c.id <> ALL (p.all_parents) -- this is the trick to exclude the endless loops
     and c.root_id = p.root_id
)
select *
from tree;

Update for Postgres 14

序号14采用了(符合标准)<代码>CYCLE的检测周期选择:

with recursive tree as (
  select id, 
         parent_id
  from hierarchy
  where parent_id is null

  union all

  select c.id, 
         c.parent_id
  from hierarchy c
     join tree p
      on c.parent_id = p.id 
)
cycle id -- track cycles for this column
   set is_cycle -- adds a boolean column is_cycle
   using path -- adds a column that contains all parents for the id
select *
from tree
where not is_cycle

如以下文件所示:is_rix/code>和。

如果你有任何漏洞,因为距离每次只增加1人,在<条码>后某个时候,距离就在循环中,距离将是周期的多。 当出现这种情况时,<代码>Child和Grandchild栏相同。 利用这一额外条件来制止再次入侵,并在你的法典其余部分将之视为错误。

页: 1 服务器样本:

declare @LinkTable table (Parent int, Child int);
insert into @LinkTable values (1, 2), (1, 3), (2, 4), (2, 5), (3, 6), (3, 7), (7, 1);

with cte as (
    select lt1.Parent, lt1.Child, lt2.Child as Grandchild
    from @LinkTable lt1
    inner join @LinkTable lt2 on lt2.Parent = lt1.Child
    union all
    select cte.Parent, lt1.Child, lt3.Child as Grandchild
    from cte
    inner join @LinkTable lt1 on lt1.Parent = cte.Child
    inner join @LinkTable lt2 on lt2.Parent = cte.Grandchild
    inner join @LinkTable lt3 on lt3.Parent = lt2.Child
    where cte.Child <> cte.Grandchild
)
select Parent, Child
from cte
where Child = Grandchild;

删除造成周期的<代码>可链接记录之一,你将发现<代码>电子不再重复任何数据。

限制补休结果

WITH EMP_CTE AS
( 

    SELECT 
        0 AS [LEVEL],   
        ManagerId, EmployeeId, Name
    FROM Employees
    WHERE ManagerId IS NULL

    UNION ALL

    SELECT 
        [LEVEL] + 1 AS [LEVEL],
        ManagerId, EmployeeId, Name
    FROM Employees e
    INNER JOIN EMP_CTE c ON e.ManagerId = c.EmployeeId 
 AND s.LEVEL < 100 --RECURSION LIMIT
) 

    SELECT  * FROM EMP_CTE WHERE [Level] = 100

这里是Kingk服务器的解决办法:

表2. 说明

CREATE TABLE MyTable
(
    [ID] INT,
    [ParentID] INT,
    [Name] NVARCHAR(255)
);

INSERT INTO MyTable
(
    [ID],
    [ParentID],
    [Name]
)
VALUES
(1, NULL,  A root ),
(2, NULL,  Another root ),
(3, 1,  Child of 1 ),
(4, 3,  Grandchild of 1 ),
(5, 4,  Great grandchild of 1 ),
(6, 1,  Child of 1 ),
(7, 8,  Child of 8 ),
(8, 7,  Child of 7 ), -- This will cause infinite recursion
(9, 1,  Child of 1 );

a. 证明准确记录为主:

;WITH RecursiveCTE
AS (
   -- Get all parents: 
   -- Any record in MyTable table could be an Parent
   -- We don t know here yet which record can involve in an infinite recursion.
   SELECT ParentID AS StartID,
          ID,
          CAST(Name AS NVARCHAR(255)) AS [ParentChildRelationPath]
   FROM MyTable
   UNION ALL

   -- Recursively try finding all the childrens of above parents
   -- Keep on finding it until this child become parent of above parent.
   -- This will bring us back in the circle to parent record which is being
   -- keep in the StartID column in recursion
   SELECT RecursiveCTE.StartID,
          t.ID,
          CAST(RecursiveCTE.[ParentChildRelationPath] +   ->   + t.Name AS NVARCHAR(255)) AS [ParentChildRelationPath]
   FROM RecursiveCTE
       INNER JOIN MyTable AS t
           ON t.ParentID = RecursiveCTE.ID
   WHERE RecursiveCTE.StartID != RecursiveCTE.ID)

-- FInd the ones which causes the infinite recursion
SELECT StartID,
       [ParentChildRelationPath],
       RecursiveCTE.ID
FROM RecursiveCTE
WHERE StartID = ID
OPTION (MAXRECURSION 0);

上文询问: