SQL 递归 CTE : preventing a recursive loop by multiple recursive references

Question

问题

我有一个递归 CTE 查询，但是在创建循环时它失败了。我已经修复了简单的循环(例如 1 -> 2 -> 1)，但无法修复更复杂的循环(例如 1 -> 2 -> 3 -> 2)。

查询详情

测试表有两列:Base 和 Parent。我想要一个包含所有祖先的列表。

如果您从 test2 开始，我的查询适用于下面的示例数据，但不是从 test1 开始。

样本数据

Base    Parent
----    ------
test1   test2
test2   test3
test3   test2

SQL 查询(我尝试的修复已在注释中标记)

;with sample_data (Base, Parent) as (
    select 'test1', 'test2'
    union select 'test2', 'test3'
    union select 'test3', 'test2'
),
nt_list (Base, Ancestor, [level]) as (
        select  Base,
                Parent Ancestor,
                1 [level]
        from    sample_data
        where   Base = 'test1' -- START HERE
        union all
        select  ntl.Base,
                nt.Parent,
                ntl.[level] + 1 [level]
        from    nt_list ntl
        join    sample_data nt on ntl.Ancestor = nt.Base
        where   nt.Parent <> ntl.Base -- fix recursive bug (e.g. 1 -> 2 -> 1)
        -- WHAT I TRIED TO ADD BUT CANNOT: (e.g. 1 -> 2 -> 3 -> 2)
        and     nt.Parent in (select Ancestor from nt_list)
)
select  distinct
        ntl.Base,
        ntl.Ancestor
from    nt_list ntl
order by Ancestor

SQL 错误:公用表表达式“nt_list”的递归成员具有多个递归引用。

Answer 1

最终版本。假设 '/'永远不会是基本名称或父名称的一部分。

;with sample_data (Base, Parent) as (
    -- TEST 1
    --        select 'test1', 'test2'
    --union   select 'test2', 'test3'
    --union   select 'test3', 'test2'
    -- TEST 2
            select 'test1', 'test2'
    union   select 'test2', 'test3'
    union   select 'test3', 'test4'
    union   select 'test3', 'test9'
    union   select 'test4', 'test5'
    union   select 'test5', 'test3'
    union   select 'test9', 'test8'
    -- TEST 3
    --        select 'test1', 'test2'
    --union   select 'test2', 'test3'
    --union   select 'test3', 'test1'
    -- TEST 4
    --        select  'test1', 'test1'
    --union   select  'test1', 'test2'
),
nt_list (Base, Ancestor, [level], [path]) as (
        select  Base,
                Parent Ancestor,
                1 [level],
                '/' + convert(varchar(max), rtrim(Base)) + '/' [path]
        from    sample_data
        where   Base = 'test1' -- START HERE
        union all
        select  ntl.Base,
                nt.Parent,
                ntl.[level] + 1 [level],
                ntl.[path] + rtrim(nt.Base) + '/'
        from    nt_list ntl
        join    sample_data nt on ntl.Ancestor = nt.Base
        where   ntl.path not like '%/' + rtrim(nt.Parent) + '/%'
)
select  distinct
        ntl.Base,
        ntl.Ancestor
from    nt_list ntl
order by Ancestor