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 имеет дочернюю компанию
Визуальное представление должно выглядеть примерно так:
![Database representation]()
В отношении красных отношений от организаций, синих отношений 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 полностью оцениваются перед основным запросом, так что это может занять много страниц
перед возможной фильтрацией в основном запросе.
