oracle - 需要一个 Oracle 分层查询，它只返回子项与搜索字符串匹配的记录的完整树

Question

这是此查询的完整示例数据集，没有任何节点与搜索字符串匹配的树修剪:

Level  parent     id     text---------------------------------------------0      0          1      toplevel1      1          2      foo1      1          3      sumthin else1      1          4      foo0      0          7      toplevel21      7          8      secondlevel1      7          9      anothersecondlevel

如果用户搜索“foo”，我需要返回以下内容:

0      0          1      toplevel1      1          2      foo1      1          4      foo

实际情况有点复杂(即我要返回的树中的三个级别)但这捕获了问题。在英语中，从文本列上的匹配节点开始，返回与搜索字符串匹配的节点的祖先树，并返回所有祖先。

我是 Oracle 的新手(至少最近是这样)，并尝试添加到 CONNECT BY 子句但没有成功 - 总是返回以下内容:

1      1          2      foo1      1          4      foo

PS - oracle 文档和示例暗示 CONNECT_BY_ROOT 将捕获祖先，但它似乎所做的只是返回顶级 (ROOT) 值。

Answer 1

要从下往上遍历，重要的一点是 CONNECT BY PRIOR 之后的值顺序)order by 用于反转输出(因为根是 foo)，distinct 删除重复的顶层值:

SELECT DISTINCT LEVEL, id, text
FROM t1
CONNECT BY PRIOR parent = id
START WITH text = 'foo'
ORDER BY LEVEL DESC

注意:如果您向 foo 添加一个 child 并切换 CONNCT BY PRIOR id = parent 您将得到 child

如果你想看到整个层次结构，你可以找到树的顶部(通过查找没有父项的行)

SELECT id
FROM t1
WHERE parent=0
CONNECT BY PRIOR parent = id
START WITH text = 'foo'

然后将其用作 START WITH id(并反转外部查询中树遍历的顺序，id = parent):

SELECT DISTINCT LEVEL, id, text
FROM t1
CONNECT BY PRIOR id = parent
START WITH id IN 
(
    SELECT id
    FROM t1
    WHERE parent=0
    CONNECT BY PRIOR parent = id
    START WITH text = 'foo'
)
ORDER BY LEVEL DESC