Нужен иерархический запрос Oracle, который возвращает только полные деревья для записей, в которых дочерние элементы соответствуют строке поиска.

Вот полный пример набора данных для этого запроса без какой-либо обрезки деревьев, где ни один узел не соответствует строке поиска:

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 - документы и примеры оракула подразумевают, что CONNECT_BY_ROOT будет захватывать предков, но все, что он делает, это возвращает значения верхнего уровня (ROOT).


oracle hierarchical-trees hierarchical-query
person bchesley    schedule 13.10.2011    source источник
comment
Вы ссылаетесь на УРОВЕНЬ как на иерархический псевдостолбец или столбец в вашей таблице?   -  person Ollie    schedule 13.10.2011
comment
Я задал аналогичный вопрос некоторое время назад - возможно, некоторые ответы помогут stackoverflow.com/questions/301817/   -  person Greg Reynolds    schedule 13.10.2011

Ответы (3)


arrow_upward
3
arrow_downward

Для обхода снизу вверх важным битом является порядок значений после 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'

затем используйте это как 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
person Kevin Burton    schedule 13.10.2011
comment
Изменение смысла CONNECT BY дает мне правильный набор результатов. Все еще борюсь с правильной сортировкой, поскольку изменение обхода дерева снизу, кажется, отменяет любую попытку сортировки вывода таким образом, что деревья по-прежнему остаются в непрерывном наборе. - person bchesley; 13.10.2011
comment
не уверен, почему сортировка не работает, возможно, вам нужно пройти сверху вниз? - person Kevin Burton; 13.10.2011
comment
С сортировкой и устранением дубликатов все готово. Спасибо всем за помощь! - person bchesley; 14.10.2011

arrow_upward
0
arrow_downward

В зависимости от того, как вы используете столбец УРОВЕНЬ (согласно моему комментарию).

Информация об иерархических запросах 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

Надеюсь, поможет...

person Ollie    schedule 13.10.2011

arrow_upward
0
arrow_downward

Мой взгляд на часть «Иерархический запрос 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)
;
person PiC    schedule 26.01.2016