一旦结果集包含某个值，就退出递归公用表表达式

发布于 2024-09-28 11:33:12 字数 715 浏览 0 评论 0原文

给定下表：

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)
)

这给出了错误：公共表表达式“Tree”的递归成员有多个递归引用。

注意：我只对自上而下感兴趣遍历。

原文

Given the following table:

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

How could I write a common table expression to start at the root (WHERE ParentID IS NULL) and traverse its descendants until the result set contains some target node (e.g., WHERE ID = n)? It's easy to start at the target node and traverse upward to the root, but that wouldn't generate the same result set. Specifically, nodes having the same parent as the target node wouldn't be included.

My first attempt was:

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)
)

Which gives the error: Recursive member of a common table expression 'Tree' has multiple recursive references.

NOTE: I'm only interested in top-down traversal.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

人生戏 2024-10-05 11:33:12

更新2：

第三次尝试在两个方向上“遍历”树。

构建从 Target 到 root 的所有 ParentID 的 CTE。然后，从树中选择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]
                  )

UPDATE 2:

A third attempt that "traverses" the tree in both directions.

Build a CTE of all ParentIDs from Target to root. Then, select from tree the nodes whose ID or Parent shows up in the short list.

--
;
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]
                  )

回复收藏 0 原文

~没有更多了~