Как выбрать строки из иерархической фильтрации запроса по значению потомка в Oracle?

Учитывая таблицу

ID       PARENT_ID      STRVAL      SUBTYPE     SUBVAL
0        null           Chicago     location    city
1        0              Best Buy    building    bestbuy
2        0              Walmart     building    walmart
3        0              Amazon      building    amazon
4        1              Macbook     object      macbook
5        2              Sausages    object      sausages
6        3              Macbook     object      macbook
7        3              Tupperware  object      tupperware

Что я пытаюсь сделать, так это запросить эту таблицу и получить все элементы с уровня 1 (здания), но мне нужно отфильтровать этот возвращаемый набор, возвращая те, у которых есть дочерние элементы, содержащие определенное значение. Следующий запрос — это то, что у меня есть до сих пор, который возвращает Best Buy, Walmart и Amazon.

SELECT * FROM (
SELECT strval, parent_id, id
FROM stores
where LEVEL = 1
CONNECT BY PRIOR id = parent_id
START WITH parent_id = 0
)

Я хотел бы получить результат, в котором один из потомков имеет подтип object и подтип macbook< /b>, поэтому из моего запроса возвращаются только Best Buy и Amazon. Я не совсем уверен, куда идти отсюда.

SQLFiddle


sql recursion oracle oracle11g hierarchical
person js1983    schedule 08.09.2017    source источник
comment
Всегда ли SUBTYPE находится в отношении 1:1 к уровням в древовидной структуре? Всегда ли object находится на уровне 2?   -  person mathguy    schedule 08.09.2017
comment
Объект может быть на уровне 3 в некоторых случаях.   -  person js1983    schedule 08.09.2017

Ответы (2)


arrow_upward
2
arrow_downward

Попробуйте изменить свое условие CONNECT BY на противоположное и начать с (т.е. START WITH) того, что вы знаете:

SELECT DISTINCT strval, parent_id, id
FROM stores
where subtype = 'building'
CONNECT BY id = prior parent_id
START WITH subtype = 'object' and subval = 'macbook';

Обновление для более общего вопроса

В комментариях вы спросили, что если начальные значения не на одном уровне?

В этом случае, я боюсь, вам придется просмотреть все дерево для каждого здания, а затем отфильтровать.

Я добавил эту строку в ваши тестовые данные:

insert into stores values (8, 4, 'Year','edition','2015');

Затем этот запрос дает ответ:

WITH whole_tree AS
       (SELECT strval,
               parent_id,
               id,
               CONNECT_BY_ROOT(strval) building,
               SYS_CONNECT_BY_PATH (subtype || ':' || subval, ',') PATH
        FROM   stores
        CONNECT BY PRIOR id = parent_id
        START WITH subtype = 'building')
SELECT distinct building
FROM   whole_tree
WHERE  PATH LIKE '%object:macbook%edition:2015%';
person Matthew McPeak    schedule 08.09.2017
comment
Это именно то, что я искал. Я возился с изменением условия CONNECT BY, как вы упомянули, но я не думал об установке требований к фильтру в разделе START WITH. Большое спасибо! - person js1983; 08.09.2017
comment
Если бы я пошел на один уровень глубже, где идентификатор 4 Macbook имел дочерний элемент подтипа «издание» и подзначение «2015», как я мог фильтровать только те, у которых подтип = «объект» и подтип = «macbook», но также подтип потомка «издания» и подзначения «2015»? - person js1983; 08.09.2017
comment
Это другая проблема/вопрос, требующая совершенно другого подхода. Вам нужен другой подход. Можете ли вы обновить свой вопрос или задать новый? Кроме того, насколько большой стол? - person Matthew McPeak; 08.09.2017

arrow_upward
1
arrow_downward

Это соединение должно дать вам объекты macbook, чьи родители являются зданиями. Не стесняйтесь выбирать только нужные столбцы:

select *
from
(
select *
from stores
where subtype = 'object' 
and strval = 'Macbook'
) macs
join
(
select *
from stores 
where subtype = 'building'
) bld
on bld.id = macs.parent_id
person Jon Ekiz    schedule 08.09.2017