SQL多条件CTE递归 [英] 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是公司有会员
视觉表示应类似于:
关于组织的红色关系，关于蓝色关系 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-> Aff
- Sub-> Sub
我仅找到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
在每个周期中，将检查每个标识符的子公司和关联公司.对新来的孩子重复一遍.
关于如何处理它的任何想法?
TL:DR: 2个表(子公司\关联公司)，2个查询.想要一个查询，从公司我可以找到所有子公司和分支机构以及subs \ affs的所有可能组合.最终的预期结果显示，只需遵循图片表示即可.
正如克雷格(Craig)所说，我修复了输出.
Edit2 :在获得了良好的帮助之后，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的每次跳跃的解释计划和成本.是否需要解决?是CTE内部的问题在工会中解决了吗?
如果我有comp-sub-sub-aff，或者发现公司的所有子公司或所有关联公司，Bob Jarvis解决方案将无法工作
将此内容从注释移至实际答案，并提供我认为您需要的内容.
几件事情..一件事情是次要的..我相信您有第一次连接的标签，是通过向后返回输出来实现的.另外，我不知道如何在最终输出中获得最后两行. 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为第一个循环提供了与其他循环不同的级别，因为它是自引用.我假设如果这是一个问题，您可以为此情况添加一些自定义逻辑.
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.
Something along the lines, 2 tables(ignoring some unique identifiers):
organizations
orgid | org_immediate_parent_orgid
1 | 2
2 | 2
3 | 1
5 | 4
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 --> affiliated_orgid is Company has affiliate
The visual representation should be something like:
On red relations from organizations, on blue relations org_affiliations.
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
returns
org_immediate_parent_orgid| orgid
1 | 2
2 | 2
3 | 1
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
returns
orgid | affiliated_orgid
2 | 3
2 | 5
So of all possible relations:
- Aff --> Aff
- Aff --> Sub
- Sub --> Aff
- Sub --> Sub
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?
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.
Any idea on how to approach it?
TL:DR: 2 tables(subsidiaries\affiliates), 2 queries. want single query where from a company i find all subsidiaries and affiliates and all possible combination of subs\affs. Final expected result show, just follow the picture representation.
Edit: As commented by Craig, I fixed the output.
Edit2: 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
Same for 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
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
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 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.
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
Which gives the following output:
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
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.
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屋！
