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

Need an Oracle hierarchical query that returns only full trees for records where children match a search string

提问by bchesley

Here's the full example data set for this query without any pruning of trees where no node matches the search string:

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

Level  parent     id     text
---------------------------------------------
0      0          1      toplevel
1      1          2      foo
1      1          3      sumthin else
1      1          4      foo
0      0          7      toplevel2
1      7          8      secondlevel
1      7          9      anothersecondlevel

I need to return the following if the user searches on 'foo':

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

0      0          1      toplevel
1      1          2      foo
1      1          4      foo

The real case is a bit more complex (i.e., three levels in the tree that I want to return) but this captures the issue. In English, return the ancestor tree for an node that matches the search string starting at the matching node on text column and return all ancestors.

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

I am new to Oracle (at least recently) and have tried adding to the CONNECT BY clause without any success - always returns the following:

我是 Oracle 的新手（至少最近），并尝试添加到 CONNECT BY 子句中，但没有成功 - 总是返回以下内容：

1      1          2      foo
1      1          4      foo

PS - the oracle docs and examples on this imply that CONNECT_BY_ROOT will capture the ancestors but all it seems to do is return top level (ROOT) values.

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

回答by Kevin Burton

To traverse from the bottom up the important bit is the order of values after the CONNECT BY PRIOR) The order byis used to reverse the output (as the root is foo) and the distinctremoves the duplicate toplevel values:

要从下往上遍历，重要的一点是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

Note: if you add a child to foo and switch the CONNCT BY PRIOR id = parent you will get the children

注意：如果您将孩子添加到 foo 并切换 CONNCT BY PRIOR id = parent，您将获得孩子

if you want to see the whole hierarchy you could find the top, of the tree (by looking for the row with no parent)

如果您想查看整个层次结构，您可以找到树的顶部（通过查找没有父级的行）

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

then use this as the START WITH id (and reverse the order of the tree traversal in the outer query, id = parent) :

然后将其用作 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

回答by PiC

My take on "Oracle hierarchical query that returns only full trees" part would be:

我对“仅返回完整树的 Oracle 分层查询”部分的看法是：

SELECT Parent_ID    
     , Child_ID 
     , INITIAL_Parent_ID
     , child_level
     , INITIAL_Parent_ID || children_CHAIN AS CONNECTION_CHAIN
FROM ( SELECT CONNECT_BY_ROOT Parent_ID AS INITIAL_Parent_ID
            , Parent_ID
            , Child_ID
            , LEVEL AS child_level
            , SYS_CONNECT_BY_PATH(Child_ID, '/') AS children_chain
       FROM REF_DECOMMISSIONS
       CONNECT BY NOCYCLE Parent_ID = PRIOR Child_ID
     )
WHERE INITIAL_Parent_ID NOT IN (SELECT Child_ID FROM REF_DECOMMISSIONS)
;

To filter the trees to the ones containing the specific children you'd need to add another condition to the last WHERE to further filter INITIAL_Parent_ID. The query would become:

要将树过滤为包含特定子项的树，您需要向最后一个 WHERE 添加另一个条件以进一步过滤 INITIAL_Parent_ID。查询将变为：

WITH ROOT_TREES AS
( SELECT Parent_ID  
       , Child_ID   
       , INITIAL_Parent_ID
       , child_level
       , INITIAL_Parent_ID || children_CHAIN AS CONNECTION_CHAIN
  FROM ( SELECT CONNECT_BY_ROOT Parent_ID AS INITIAL_Parent_ID
              , Parent_ID
              , Child_ID
              , LEVEL AS child_level
              , SYS_CONNECT_BY_PATH(Child_ID, '/') AS children_chain
         FROM REF_DECOMMISSIONS
         CONNECT BY NOCYCLE Parent_ID = PRIOR Child_ID
       )
  WHERE INITIAL_Parent_ID NOT IN (SELECT Child_ID FROM REF_DECOMMISSIONS)
)
SELECT * 
  FROM root_trees 
WHERE INITIAL_Parent_ID IN (SELECT INITIAL_Parent_ID 
                              FROM root_trees 
                             WHERE Child_ID = 123)
;

回答by Ollie

Depending upon your use of the LEVEL column (as per my comment).

取决于您对 LEVEL 列的使用（根据我的评论）。

Info on Oracle Hierarchical Queries: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm

有关 Oracle 分层查询的信息：http: //download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm

This returns what you ask for if LEVEL is the Oracle pseudocolumn:

如果 LEVEL 是 Oracle 伪列，这将返回您要求的内容：

WITH t AS (SELECT 0 AS parent,
                  1 AS id,
                  'toplevel' AS text FROM DUAL
           UNION
           SELECT 1 AS parent,
                  2 AS id,
                  'foo' AS text FROM DUAL
           UNION
           SELECT 1 AS parent,
                  3 AS id,
                  'sumthin else' AS text FROM DUAL
           UNION
           SELECT 1 AS parent,
                  4 AS id,
                  'foo' AS text FROM DUAL
           UNION
           SELECT 0 AS parent,
                  7 AS id,
                  'toplevel2' AS text FROM DUAL
           UNION
           SELECT 7 AS parent,
                  8 AS id,
                  'secondlevel' AS text FROM DUAL
           UNION
           SELECT 7 AS parent,
                  9 AS id,
                  'anothersecondlevel' AS text FROM DUAL
          ) 
SELECT UNIQUE
       level,
       parent,
       id,
       text
  FROM t
 START WITH text = 'foo'
 CONNECT BY PRIOR parent = id
 ORDER BY parent;

Returns:

返回：

LEVEL PARENT ID TEXT
    2      0  1 toplevel
    1      1  2 foo     
    1      1  4 foo     

If LEVEL is a column in your table then:

如果 LEVEL 是表中的一列，则：

WITH t AS (SELECT 0 AS tlevel,
                  0 AS parent,
                  1 AS id,
                  'toplevel' AS text FROM DUAL
           UNION
           SELECT 1 AS tlevel,
                  1 AS parent,
                  2 AS id,
                  'foo' AS text FROM DUAL
           UNION
           SELECT 1 AS tlevel,
                  1 AS parent,
                  3 AS id,
                  'sumthin else' AS text FROM DUAL
           UNION
           SELECT 1 AS tlevel,
                  1 AS parent,
                  4 AS id,
                  'foo' AS text FROM DUAL
           UNION
           SELECT 0 AS tlevel,
                  0 AS parent,
                  7 AS id,
                  'toplevel2' AS text FROM DUAL
           UNION
           SELECT 1 AS tlevel,
                  7 AS parent,
                  8 AS id,
                  'secondlevel' AS text FROM DUAL
           UNION
           SELECT 1 AS tlevel,
                  7 AS parent,
                  9 AS id,
                  'anothersecondlevel' AS text FROM DUAL
          ) 
SELECT UNIQUE
       tlevel,
       parent,
       id,
       text
  FROM t
 START WITH text = 'foo'
 CONNECT BY PRIOR parent = id
 ORDER BY parent;

Returns:

返回：

TLEVEL PARENT ID TEXT
     0      0  1 toplevel
     1      1  2 foo     
     1      1  4 foo     

Hope it helps...

希望能帮助到你...