SQL Server CTE父子递归 [英] Sql Server CTE Parent Child recursive

问题描述

我具有以下表结构：

create table Test(
  ParentId int,
  ChildId int
 )

insert into Test(ParentId, ChildId)
select 1, NULL
union
select 1, 2
union
select 1, 3
union
select 2, NULL
union
select 2, 5
union
select 5, 6
union 
select 6, 5
union 
select 6, 8

I正在尝试建立所有父子DIRECT和INDIRECT关系的结果集。因此，假设我传递了ParentID = 2的参数，我希望返回的结果集如下所示：

I'm trying to build a result set of all parent child DIRECT and INDIRECT relationships. So suppose I pass the parameter of ParentID = 2, I would like the result set to return exactly as below:

ParentId    ChildId
-------------------
2           NULL
2           5
5           6
6           8
1           2

因此，基本上显示了所有可能的链接一张父母ID = 2的表。
从父母本身开始，它具有子ID，然后与子ID 6具有其他关系。另外，父ID 2也属于父ID 1，这也应显示在结果集中。请注意，这种关系可能会扩展到 N个级别。希望您理解我在这里要实现的目标，否则请告诉我，以便我能更清楚地解释。

So basically this shows all possible links that can be found in one table for Parent ID = 2. Starting off with Parent itself, it has Child Id which then has other relations with Child Id 6. Also the parent Id 2 falls under parent id 1 which should also show up in result set. Please note the relationship could expand to N number of levels. I hope you understand what I am trying to achieve here, if not then please let me know so that I can explain more clearly.

到目前为止，我在下面提出了建议递归查询，但引发以下错误：

So far I have come up with below recursive query but it throws an error which is stated below:

DECLARE @ID INT = 2

;WITH MyCTE
AS (
    SELECT ParentId
        ,ChildId
    FROM Test
    WHERE ParentId = @ID

    UNION ALL

    SELECT T.ParentId
        ,Test.ChildId
    FROM Test
    INNER JOIN MyCTE T ON Test.ParentID = T.ChildId
    WHERE Test.ParentID IS NOT NULL
    )
SELECT *
FROM MyCTE

Error:
The statement terminated. The maximum recursion 100 has been exhausted before statement completion

我已将代码放在这里的SQLFiddle 供您测试和尝试。

I have put up the code on SQLFiddle here for you guys to test and try.

我真的很感谢指导和帮助我实现理想结果的任何人。

I would really appreciate anyone who guides and helps me in achieving my desired result.

推荐答案

正如#Mikael Eriksson所说：您的数据中有一个循环引用。5是6的父级，6是5的父级。

As #Mikael Eriksson said: "You have a circular references in your data. 5 is parent to 6 and 6 is parent to 5."

在递归部分中，您还输出 ParentId ，而不是刚刚找到的行。

Also in recursive part you output ParentId from previous step, not from just found rows.

declare @Test table (ParentId int, ChildId int)

insert into @Test (ParentId, ChildId)
select 1, null
union all
select 1, 2
union all
select 1, 3
union all
select 2, null
union all
select 2, 5
union all
select 5, 6
union all
--select 6, 5
--union all
select 6, 8

declare @id int = 2

;with MyCTE as (
    select ParentId, ChildId
    from @test
    where ParentId = @id

    union all

    select t2.ParentId, t2.ChildId
    from MyCTE t1
    inner join @Test t2 on t1.ChildId = t2.ParentId
)

select * from MyCTE

我没有做的另一件事不知道为什么会有这样的行，其中 ChildId 为空，而 ParentId 不为空。怎么可能？

Another thing I didn't understand is why do you have such rows where ChildId is null and ParentId is not null. How can it be possible?

这是否意味着您有未知的物品，其父母是已知的？

Does it mean that you have unknown items whose parent is known?

这篇关于SQL Server CTE父子递归的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！