Выход из рекурсивного общего табличного выражения после того, как набор результатов содержит некоторое значение

Учитывая следующую таблицу:

create table TreeNode
(
  ID int not null primary key,
  ParentID int null foreign key references TreeNode (ID)
)

Как мне написать обычное табличное выражение, чтобы оно начиналось с корня (WHERE ParentID IS NULL) и проходило по его потомкам до тех пор, пока набор результатов не будет содержать некоторый целевой узел (например, WHERE ID = n)? Легко начать с целевого узла и перейти вверх к корню, но это не приведет к тому же набору результатов. В частности, узлы, имеющие того же родителя, что и целевой узел, не будут включены.

Моя первая попытка была:

with Tree as
(
  select
    ID,
    ParentID
  from
    TreeNode
  where
    ParentID is null
  union all select
    a.ID,
    a.ParentID
  from
    TreeNode a
    inner join Tree b
      on b.ID = a.ParentID
  where
    not exists (select * from Tree where ID = @TargetID)
)

Что дает ошибку: Recursive member of a common table expression 'Tree' has multiple recursive references.

ПРИМЕЧАНИЕ. Меня интересует только обход сверху вниз.


sql-server tsql common-table-expression recursive-query sql-server-2005
person Daniel    schedule 21.10.2010    source источник

Ответы (1)


arrow_upward
1
arrow_downward

ОБНОВЛЕНИЕ 2:

Третья попытка, которая «обходит» дерево в обоих направлениях.

Создайте CTE всех ParentIDs от Target до root. Затем выберите из tree nodes, чьи ID или Parent отображаются в коротком списке.

--
;
WITH    Tree
          AS ( SELECT   ID
                       ,ParentID
               FROM     TreeNode
               WHERE    [ID] = @targetId
               UNION ALL
               SELECT   a.ID
                       ,a.ParentID
               FROM     TreeNode a
                        INNER JOIN Tree b ON b.ParentID = a.ID
             )
    SELECT  *
    FROM    [dbo].[TreeNode] n
    WHERE  EXISTS (SELECT *
                   FROM [Tree] t
                   WHERE [t].[ID] = [n].[ID]
                         OR [t].[ID] = [n].[ParentID]
                  )
person Brad    schedule 21.10.2010
comment
Я не думаю, что мы можем предположить, что узлы выше в дереве всегда имеют более низкие идентификаторы. - person Daniel; 21.10.2010
comment
@ Даниэль, так и заметил. Я обновил свой запрос. Это все еще работает в моем небольшом наборе образцов. - person Brad; 21.10.2010
comment
Вопрос конкретно в том, как пройти сверху вниз. Я упоминаю проблему с восходящим движением в ОП. - person Daniel; 21.10.2010
comment
@ Даниэль, очень верно. Я пропустил это. Я провел еще одно испытание обхода вниз. Он начинается с обхода вверх, но затем возвращается и получает все узлы, которые принадлежат по пути. - person Brad; 21.10.2010
comment
Разве эта последняя строка не должна быть: [t].[ParentID] = [n].[ID]? - person Daniel; 21.10.2010
comment
@ Даниэль, если вы сделаете последнюю строку [t].[ParentID] = [n].[ID], вы будете оттягивать только те узлы, где [t].[ID] = [n].[ID] (по крайней мере, так в моем тесте). - person Brad; 21.10.2010
comment
Ах, ты прав. Прости. Я перевернул псевдонимы в уме. - person Daniel; 21.10.2010