在数据库中，每个标识符都有以下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作为公司的母公司作为子公司
org_affiliations
orgid | affiliated_orgid
2 | 3
2 | 5
4 | 1
1 | 5
orgid-> affiliated_orgid是公司有成员(member)
视觉表示应类似于:
关于来自组织的红色关系，关于蓝色关系 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
在每个周期中，将检查每个标识符的子公司和关联公司。对新来的 child 重复一遍。
对如何处理有任何想法吗？
TL:DR: 2个表(子公司\关联公司)，2个查询。想要一个查询，从公司我可以找到所有子公司和分支机构以及subs\affs的所有可能组合。最终的预期结果显示，只需遵循图片表示即可。
编辑:如Craig所评论，我修复了输出。
编辑2:在获得良好帮助之后，Craig和Bob Jarvis给了我继续遇到问题的机会。
对于收集子公司，以下代码可完美运行，并且输出如我所愿:
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 developer中，我去检查了“解释计划和从7到400k的每次跳跃的成本，只需添加” union all”。
鲍勃·贾维斯(Bob Jarvis)解决方案在我有comp-sub-sub-aff或找到公司的所有子公司或所有分支机构的情况下不起作用
最佳答案
将其从评论移至实际答案，并提供我认为您需要的东西。
几件事情..一件是次要的..我相信您可以通过向后返回输出来获得第一次连接的标签。另外，我不知道如何在最终输出中获得最后两行。 4是5的 parent ，而不是 child ，那为什么会出现呢？如果不存在，那么最后一行也不会很好。
如果我阅读正确，则可以使用以下方法:
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
最大的事情是，这两个 table 彼此相对设置(组织与父级有联系，从属关系与子级有联系)。因此，我在WITH子句中将它们设置为相同的格式，然后在组合集上使用connect by。
另外，由于某种原因，Oracle为第一个循环提供了与其他循环不同的级别，因为它是一个自引用。我假设如果这是一个问题，您可以为此情况添加一些自定义逻辑。
关于SQL多条件CTE递归，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/6954779/