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

Question

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

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

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

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

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

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

1      1          2      foo
1      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 添加一个子级并切换CONNCT BY PRIOR id = Parent

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

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

然后使用它作为 STARTWITH 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

Answer 2

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

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

这个如果 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;

返回：

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

如果 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;

返回：

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

希望有帮助...

Answer 3

我对“仅返回完整树的 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)
;

要将树过滤到包含特定子级的树，您需要在最后一个 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)
;