SQL Multi Condition CTE Recursion

В базе данных у меня есть два следующих фрагмента информации для каждого идентификатора. Компания, которая их контролирует, и компании, у которых есть небольшие блоки контроля.

Что-то вдоль строк, 2 таблицы (игнорируя некоторые уникальные идентификаторы):

организации

orgid | org_immediate_parent_orgid
1     | 2
2     | 2
3     | 1
5     | 4

Отношение orgid → org_immediate_parent_orgid означает, что у компании есть родитель. По мне это соответствует только org_immediate_parent_orgid → orgid, у родителя компаний есть дочернее предприятие

org_affiliations

orgid | affiliated_orgid
2     | 3
2     | 5
4     | 1
1     | 5

orgid → affiliated_orgid имеет дочернюю компанию

Визуальное представление должно выглядеть примерно так:

В отношении красных отношений от организаций, синих отношений org_affiliations.

Если вы хотите, чтобы все компании, принадлежащие 2 (или дочернему сыну 2), имели в них какую-то часть:

select m.org_immediate_parent_orgid
,m.orgid
from oa.organizations m
where m.org_immediate_parent_orgid is not null
start with m.orgid in (Identifiers)
connect by nocycle prior  m.orgid=m.org_immediate_parent_orgid

возвращает

org_immediate_parent_orgid| orgid
1                         | 2
2                         | 2
3                         | 1

Если вы хотите, чтобы все компании были 2 (или аффилированным сыном 2), в нем есть часть:

select aff.orgid,aff.affiliated_orgid
from oa.org_affiliations aff
where aff.affiliated_orgid is not null
start with aff.orgid in(Identifiers)
connect by nocycle prior  aff.affiliated_orgid =aff.orgid

возвращает

orgid | affiliated_orgid
2     | 3
2     | 5

Итак, из всех возможных отношений:

Aff → Aff
Aff → Sub
Sub → Aff
Sub → Sub

Я нахожу Sub → Sub (дочерние компании дочерних компаний), отношения (2 → 1 и отношения 1 → 3) и Aff → Aff, отношения (2 → 3 и отношения 2 - → 5). Также он требует от меня двух отдельных запросов.

Как я могу вытащить все возможные отношения в одном рекурсивном запросе?

Если я передаю идентификатор 2, должно быть возможно следующее возвращение:

Relation | Loop| orgid | children
Sub      | 1   | 2     |2
Sub      | 1   | 2     |1
Aff      | 1   | 2     |3
Aff      | 1   | 2     |5
Sub      | 2   | 1     |3
Aff      | 2   | 1     |5

В каждом цикле будет проверяться субтитры и ветки для каждого идентификатора. Повторите для новых детей.

Любая идея о том, как подойти к ней?

TL: DR: 2 таблицы (дочерние компании\ветки), 2 запроса. нужен один запрос, где от компании я нахожу все дочерние компании и ветки и все возможные комбинации subs\affs. Окончательный ожидаемый результат показывает, просто следуйте представлению изображения.

Изменить: Как прокомментировал Крейг, я исправил вывод.

Edit2: Следуя за хорошей помощью, Крейг и Боб Джарвис дали мне возможность столкнуться с проблемами.

Для сбора дочерних компаний следующий код работает безупречно, и вывод такой, какой я хотел бы:

with
relations as
(
select orgid as children,org_immediate_parent_orgid as orgid,'Sub' as relation
from oa.organizations 
)
select distinct relation, level, orgid, children
from relations
where children is not null
start with orgid in (identifier)
connect by
nocycle prior children = orgid
order by 2,3,4

То же самое для AFF:

with
relations as
(
select affiliated_orgid as children, orgid as orgid,'Aff' as relation
from oa.org_affiliations    
)
select distinct relation, level, orgid, children
from relations
where children is not null
start with orgid in (identifier)
connect by
nocycle prior children = orgid
order by 2,3,4

но не может "объединить все"?

with
relations as
(
select orgid as children,org_immediate_parent_orgid as orgid,'Sub' as relation
from oa.organizations

UNION ALL

select affiliated_orgid as children, orgid as orgid,'Aff' as relation
from oa.org_affiliations    
)
select distinct relation, level, orgid, children
from relations
where children is not null
start with orgid in (identifier)
connect by
nocycle prior children = orgid
order by 2,3,4

В sql-разработчике я пошел и проверил "объяснить план и стоимость каждого перехода с 7 до 400 тыс., просто добавив" union all ". Любой рабочий стол? Проблема в CTE, в союзе alL?

Решение Боба Джарвиса не будет работать в тех случаях, когда у меня есть comp-sub-sub-aff, или он находит все дочерние компании компании или всех веток

Ответ 1

Перемещение этого из комментария в фактический ответ и предоставление того, что, по вашему мнению, вам нужно.

Несколько вещей.. один из них незначительный. Я считаю, что у вас есть метки вашего первого соединения, возвращая выходные данные назад. Кроме того, я не понимаю, как вы получаете последние две строки в своем конечном выпуске. 4 является родителем 5, а не ребенком, так почему он появляется? И если его там нет, то последняя строка будет не так.

Если я правильно его читаю, вы можете использовать что-то вроде:

with
relations as
(
    select
        orgid,
        org_immediate_parent_orgid parent_id,
        'Sub' relation
    from
        organizations
    union all
    select
        orgid,
        null parent_id,
        'Aff' relation
    from
        org_affiliations
    where
        orgid not in (
            select affiliated_orgid
            from org_affiliations
        )
    union all
    select
        affiliated_orgid orgid,
        orgid parent_id,
        'Aff' relation
    from
        org_affiliations
)
select distinct relation, level, parent_id, orgid
from relations
where parent_id is not null
start with orgid = 2
connect by
    nocycle prior orgid = parent_id
order by 2,3,4

Что дает следующий вывод:

RELATION|LEVEL|PARENT_ID|ORGID
Sub     |1    |2        |2
Sub     |2    |2        |1
Aff     |2    |2        |3
Aff     |2    |2        |5
Sub     |3    |1        |3
Aff     |3    |1        |5

Самое главное, что две таблицы были настроены напротив друг друга (организации имели ссылку на родителя, у веток была ссылка на ребенка). Поэтому я делаю их в том же формате в предложении WITH, а затем используя соединение по объединенному набору.

Кроме того, по какой-то причине Oracle дает первый цикл на другом уровне, чем другие, поскольку он является самоналожением. Я предполагаю, что если это проблема, вы можете добавить в нее некоторую пользовательскую логику.

Ответ 2

Здесь начинается на нем:

select 'SUB -> SUB' AS TYPE,
       m.orgid AS ORGID,
       m.org_immediate_parent_orgid AS PARENT_OR_AFF
  from organizations m
  where m.org_immediate_parent_orgid is not NULL
  start with m.orgid in (2)
  connect by nocycle prior m.orgid = m.org_immediate_parent_orgid
UNION ALL
select 'AFF -> AFF' AS TYPE,
       aff.orgid AS ORGID,
       aff.affiliated_orgid AS PARENT_OR_AFF
  from org_affiliations aff
  where aff.affiliated_orgid is not NULL
  start with aff.orgid IN (2)
  connect by nocycle prior aff.affiliated_orgid = aff.orgid;

Если вы добавите подзапросы, чтобы получить оставшиеся отношения, вам должно быть хорошо идти.

Поделитесь и наслаждайтесь.

Ответ 3

непроверенная. Создайте представление, чтобы упростить извлечение данных из ваших 2 таблиц.

create view related(orgid, relatedid) as 
    select orgid, org_immediate_parent_orgid as relatedid from organizations
    union distinct
    select orgid, affiliated_orgid as relatedid from affiliated;

Теперь мы можем использовать это, чтобы итеративно находить все интересные орды легко.

with recursive related_recursive(orgid, relatedid) as (
    select orgid, relatedid from related where relatedid = 2
    union
    select r.origid, rr.relatedid from related_recursive rr, related r
        where rr.orig = r.relatedid
) 
select orgid from related_recursive;

В этом случае вы можете удалить связанный столбец related_recursive, но он полезен и необходимо, если вы хотите удалить или изменить часть и выбрать * из related_recursive.

Просто помните, что CTE полностью оцениваются перед основным запросом, так что это может занять много страниц перед возможной фильтрацией в основном запросе.