SQL 多条件 CTE 递归 [英] SQL Multi Condition CTE Recursion

问题描述

我在数据库中为每个标识符提供了以下 2 条信息.控制他们的公司，以及他们拥有少量控制权的公司.

I the database i have the 2 following pieces of information for each identifier. The company that controls them, and companies where they have small bits of control.

大致上，2个表(忽略一些唯一标识符):

Something along the lines, 2 tables(ignoring some unique identifiers):

组织

orgid | org_immediate_parent_orgid
1     | 2
2     | 2
3     | 1
5     | 4

关系 orgid --> org_immediate_parent_orgid 表示公司有父级.对我来说，它只有相关的 org_immediate_parent_orgid --> 公司的母公司作为子公司的 orgid

The relation orgid --> org_immediate_parent_orgid means company has parent. Por me its relevant only org_immediate_parent_orgid --> orgid the parent of the companies has as subsidiary

org_affiliations

orgid | affiliated_orgid
2     | 3
2     | 5
4     | 1
1     | 5

orgid --> associated_orgid 是公司有关联公司

orgid --> affiliated_orgid is Company has affiliate

视觉表现应该是这样的:

The visual representation should be something like:

关于组织的红色关系，关于org_affiliations的蓝色关系.

On red relations from organizations, on blue relations org_affiliations.

如果想要让 2(或 2 的子公司)拥有的所有公司都参与其中:

If Want to get all companies owned by 2(or subsidiary son of 2) has some part in it them:

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 的附属儿子)，它们都参与其中:

If Want to get all companies were 2(or affiliated son of 2) has some part in it them:

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 --> Aff
• 子 --> 子

我只找到Sub --> Sub(子公司的子公司)，关系(2 --> 1和关系1 --> 3)和Aff --> Aff，关系(2 --> 3和关系2 --> 5).它还需要我进行 2 个单独的查询.

I only find Sub --> Sub (subsidiaries of subsidiaries), relations (2 --> 1 and relations 1 --> 3) and Aff --> Aff, relations (2 --> 3 and relations 2 --> 5). Also it requires me 2 separate queries.

如何在一个递归查询中提取所有可能的关系?

How can i pull all possible relations in one single recursive query?

如果我传递标识符 2，则可能会返回以下结果:

If i pass identifier 2 it should be possible the following return:

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

在每个周期中都会检查每个标识符的订阅者和附属机构.对新的孩子重复.

In each cycle would check subs and affiliates for each identifier. Repeat for the new children.

知道如何处理吗?

TL:DR: 2 个表(子公司附属公司)，2 个查询.想要单个查询从一家公司我找到所有子公司和附属公司以及所有可能的 subsaffs 组合.最终预期效果展示，按照图片展示即可.

TL:DR: 2 tables(subsidiariesaffiliates), 2 queries. want single query where from a company i find all subsidiaries and affiliates and all possible combination of subsaffs. Final expected result show, just follow the picture representation.

正如 Craig 所评论的，我修复了输出.

As commented by Craig, I fixed the output.

Edit2:在 Craig 和 Bob Jarvis 的帮助下，我继续遇到问题.

Following on the good help Craig and Bob Jarvis gave i continue to run into problems.

对于收集子公司，以下代码完美无缺，输出如我所愿:

For gathering subsidiaries, the following code works flawlessy, and the output is as i would like:

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"?

but cant have "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 developer 中，我去检查解释从 7 到 400k 的每次跳跃的计划和成本，只需添加union all".任何解决方法?CTE 内部的问题，在 union all 中?

In sql developer i went and check "explain plan and cost from each jump from 7 to 400k, just by adding "union all". Any workarround? Is the problem inside the CTE, in the union alL?

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

Bob Jarvis solution wont work in cases where i have comp-sub-sub-aff, or it finds all subsidiaries of company or all affiliates

推荐答案

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

Moving this from a comment to an actual answer and providing what I believe you need.

有几件事..一个是次要的..我相信您通过向后返回输出获得了第一次连接的标签.另外，我不明白您如何获得最终输出中的最后两行.4 是 5 的父母，而不是孩子，所以为什么会出现?如果它不存在，那么最后一行也不会如此.

A couple things.. one is minor.. I believe you have the labels of your first connect by returns output backwards. Also, I don't get how you get the last two rows in your final output. 4 is a parent of 5, not a child, so why does it show up? And if it isn't there, then the last line won't be as well.

如果我没看错，您可以使用以下内容:

If I am reading it correctly, you can use something like:

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.

The biggest thing is that the 2 tables were set up opposite of each other (organizations had a link to the parent, affiliations had a link to the child). So I am making them into the same format in the WITH clause, and then using the connect by on the combined set.

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

Also, for some reason Oracle gives the first loop a different level than the others since it is a self reference. I am assuming that if this is a problem, you can put in some custom logic for this case.

这篇关于SQL 多条件 CTE 递归的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！