Требуется иерархический запрос Oracle, который возвращает только полные деревья для записей, где дочерние элементы соответствуют поисковой строке
Вот полный пример набора данных для этого запроса без обрезки деревьев, где ни один узел не соответствует строке поиска:
Уровень родительского идентификатора текста --------------------------------------------- 0 0 1 верхнего уровня 1 1 2 фу 1 1 3 еще 1 1 4 фу 0 0 7 toplevel2 1 7 8 второй уровень 1 7 9 другой
Мне нужно вернуть следующее, если пользователь выполняет поиск по 'foo':
0 0 1 верхнего уровня 1 1 2 фу 1 1 4 фу
Реальный случай немного сложнее (т.е. три уровня в дереве, которые я хочу вернуть), но это отражает проблему. На английском языке верните дерево предков для узла, который соответствует строке поиска, начиная с соответствующего узла в текстовом столбце, и верните всех предков.
Я новичок в Oracle (по крайней мере, недавно) и безуспешно пытался добавить к предложению CONNECT BY - всегда возвращает следующее:
1 1 2 фу 1 1 4 фу
PS - документы оракула и примеры на это подразумевают, что CONNECT_BY_ROOT будет захватывать предков, но все, что кажется, это сделать, это вернуть значения верхнего уровня (ROOT).
3 ответа
Чтобы пройти снизу вверх, важным битом является порядок значений после 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 = 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
В зависимости от того, как вы используете колонку 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
Надеюсь, поможет...
Мой взгляд на часть "Иерархический запрос 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)
;