SQL 多条件 CTE 递归

Question

我在数据库中为每个标识符提供了以下 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 --> 附属的_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 --> 子

子 --> Aff

子 --> 子

我只找到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 重复。

关于如何处理它的任何想法？

电话:DR: 2 个表(子公司\附属公司)，2 个查询。想要单个查询，我可以从一家公司找到所有子公司和附属公司以及所有可能的 subs\affs 组合。最终的预期结果展示，只需按照图片表示即可。

编辑:正如克雷格评论的那样，我修复了输出。

编辑 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”。任何解决方法？CTE内部的问题，在union all中？

Bob Jarvis 解决方案在我有 comp-sub-sub-aff 的情况下不起作用，或者它找到公司的所有子公司或所有附属公司

Answer 1

将此从评论转变为实际答案并提供我认为您需要的内容。

有几件事..一个是次要的..我相信您通过向后返回输出获得了第一个连接的标签。另外，我不明白您如何在最终输出中获得最后两行。 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 为第一个循环提供了与其他循环不同的级别，因为它是一个自引用。我假设如果这是一个问题，您可以为这种情况添加一些自定义逻辑。