SQL Recursive CTE: предотвращение рекурсивного цикла с помощью нескольких рекурсивных ссылок

Вопрос

У меня есть рекурсивный запрос CTE, но он не работает при создании цикла. Я уже исправил простые циклы (например, 1 -> 2 -> 1), но не могу исправить более сложные циклы (например, 1 -> 2 -> 3 -> 2).

Детали запроса

В тестовой таблице есть два столбца: Базовый и Родительский. Мне нужен список всех предков.

Мой запрос работает с образцом данных ниже, если вы начинаете с 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 имеет несколько рекурсивных ссылок.


sql common-table-expression recursive-query
person Peet Brits    schedule 18.02.2013    source источник

Ответы (4)


arrow_upward
3
arrow_downward

Окончательная версия. Предполагая, что '/' никогда не будет частью базового или родительского имени.

;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
person Peet Brits    schedule 19.02.2013

arrow_upward
2
arrow_downward

Вы можете использовать

;WITH nt_list (Base, Ancestor, [level], cycle, path)
     AS (SELECT Base,
                Parent                                                            Ancestor,
                1                                                                 [level],
                0                                                                 AS cycle,
                CAST('.' AS VARCHAR(max)) + ISNULL(Parent, '') + '.' + Base + '.' AS [path]
         FROM   NoteTest
         WHERE  Base = 'test1'
         UNION ALL
         SELECT ntl.Base,
                nt.Parent,
                ntl.[level] + 1                   [level],
                CASE
                  WHEN ntl.[path] LIKE '%.' + LTRIM(nt.Base) + '.%' THEN 1
                  ELSE 0
                END                               AS cycle,
                ntl.[path] + LTRIM(nt.Base) + '.' AS [path]
         FROM   nt_list ntl
                JOIN NoteTest nt
                  ON ntl.Ancestor = nt.Base
                     AND ntl.cycle = 0)
SELECT ntl.Base,
       ntl.Ancestor
FROM   nt_list ntl
ORDER  BY Ancestor
person Martin Smith    schedule 18.02.2013
comment
Ваш сценарий не работает для цепочки длиннее 3 (например, попробуйте 1- ›2-› 3- ›4), но я вижу, что вы сделали с путем, и могу исправить это для себя. - person Peet Brits; 19.02.2013
comment
Также нет необходимости в cycle. Достаточно хорошего like. Выложил финальную версию. - person Peet Brits; 19.02.2013

arrow_upward
0
arrow_downward

Мой текущий обходной путь - добавить ограничение уровня (добавление and ntl.[level] <= 100 к запросу) и позволить select distinct удалить повторяющиеся записи.

person Peet Brits    schedule 18.02.2013

arrow_upward
0
arrow_downward

Этот чехол идеален, но, как и для других предметов, например:

    select 'test1', 'test2'
         union select 'test2', 'test3'
         union select 'test3', 'test4'
         union select 'test4', 'test5'
         union select 'test10', 'test11'
         union select 'test11', 'test30'
         ...

Результат должен быть:

    test1   test1 <- adding this
    test1   test2
    test1   test3
    test1   test4
    test1   test5
    test10  test10 <- adding this to multiple bases
    test10  test11
    test10  test30
person Juliano    schedule 26.06.2013
comment
Запрос where с комментарием START HERE определяет, где начинается запрос. Обновите его до чего-то вроде where Base not in (select Parent from sample_data), чтобы включить все элементы (обратите внимание, что это исключит циклы, но это будет работать для вашего примера). Вы также можете изменить order by в самом конце на order by Base, Ancestor. - person Peet Brits; 08.07.2013
comment
Другими словами, вы должны указать запросу, где он должен начинаться. - person Peet Brits; 08.07.2013