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 --> 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 -->阿夫阿夫
• -->子子
• -->爱
• 夫子--> 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 个查询。我想要一个查询，从一家公司的哪里可以找到所有子公司和附属公司以及所有可能的子公司\员工组合。最终的预期结果显示，只需按照图片表示即可。

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

编辑2：在克雷格和鲍勃·贾维斯提供的良好帮助之后，我继续遇到问题。

对于收集子公司，以下代码工作完美，输出如我所愿：

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

但不能有“union all”？

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到400k的每次跳跃的计划和成本，只需添加“union all”。有任何解决方法吗？问题在CTE内部，在union all中吗？

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

Answer 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子句中将它们设置为相同的格式，然后在组合集上使用connect by。

另外，出于某种原因，Oracle 为第一个循环提供了与其他循环不同的级别，因为它是自引用。我假设如果这是一个问题，您可以为此情况添加一些自定义逻辑。

Answer 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;

如果您添加子查询来获取剩余的关系，那么您应该可以顺利进行。

分享并享受。

Answer 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 的 relatedid 列，但它很有用
如果您想删除或更改 where 部分并从 related_recursive 选择 *，则这是必需的。

请记住，CTE 在主查询之前进行了全面评估，因此这可能会拉入许多页面
在主查询中最终过滤之前。