В базе данных у меня есть 2 следующих фрагмента информации для каждого идентификатора. Компания, которая их контролирует, и компании, в которых они имеют небольшой контроль.
Что-то вроде строк, 2 таблицы (игнорируя некоторые уникальные идентификаторы):
организации
orgid | org_immediate_parent_orgid
1 | 2
2 | 2
3 | 1
5 | 4
Отношение orgid --> org_immediate_parent_orgid означает, что у компании есть родитель. Для меня это актуально только org_immediate_parent_orgid --> orgid материнской компании в качестве дочерней
организация
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
Итак, из всех возможных отношений:
- Афф --> Афф
- Афф --> Саб
- Саб --> Афф
- Подпрограмма --> Подпрограмма
Я нахожу только Sub --> Sub (дочерние дочерние), отношения (2 --> 1 и отношения 1 --> 3) и Aff --> Aff, отношения (2 --> 3 и отношения 2 --> 5 ). Также мне требуется 2 отдельных запроса.
Как я могу получить все возможные отношения в одном рекурсивном запросе?
Если я передам идентификатор 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
То же самое для АФФ:
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 тыс., Просто добавив «союз всех». Есть ли обходной путь? Проблема внутри CTE, в союзе все?
Решение Боба Джарвиса не будет работать в случаях, когда у меня есть comp-sub-sub-aff, или оно находит все дочерние компании компании или все филиалы
