问题

我正在尝试创建我认为是相当基本的层次查询，但结果不是我预期的。

我有一个合并公司的表，想要获取如果一个合并公司随后被合并到另一家公司，则获取其传递的ID。

从这个基本数据开始：

| 公司ID | 合并的公司ID |
| - | - |
| 21 | 10 |
| 21 | 22 |
| 22 | 45 |

其中公司10已合并到公司21。

我想生成一个查询，找到任何传递的合并。显示公司45现在也被视为合并到公司21，因为它的母公司已合并到21。

| 公司ID | 合并的公司ID |
| - | - |
| 21 | 10 |
| 21 | 22 |
| 21 | 45 |
| 22 | 45 |

我一直在尝试使用connect by查询，但得到意外的结果。

```sql
with base as (
	select
		company_id,
		merged_company_id
	from
		merged_companies
	where 
		merged_companies.company_id in ('21','22')
)
select
	*
from
	base
connect by
	prior base.company_id = base.merged_company_id
order by
	base.company_id, base.merged_company_id

我搞不清楚为什么它给我这些结果。与我寻找的21/45配对不同，我得到了重复的22/45。

我觉得我对这些层次查询有一个基本的误解。我假设这里不需要 start with，因为我想获取所有记录的层次结构。

<details>
<summary>英文:</summary>

I&#39;m trying to make what I think is a fairly basic hierarchical query, but the results aren&#39;t what I&#39;m expecting.

I have a table of merged companies and want to get the transitive ID&#39;s if a merged company has then been merged into another company.

Starting with this base data:

| company_id | merged_company_id |
| - | - |
| 21 | 10 |
| 21 | 22 |
| 22 | 45 |

Where company 10 has been merged into company 21.

I want to generate a query that finds any transitive mergers as well. Showing that company 45 is now also considered merged into company 21, since it&#39;s parent company has been merged into 21.

| company_id | merged_company_id |
| - | - |
| 21 | 10 |
| 21 | 22 |
| 21 | 45 |
| 22 | 45 |

I&#39;ve been playing around with connect by queries, but getting unexpected results.

```sql
with base as (
	select
		company_id,
		merged_company_id
	from
		merged_companies
	where 
		merged_companies.company_id in (&#39;21&#39;,&#39;22&#39;)
)
select
	*
from
	base
connect by
	prior base.company_id = base.merged_company_id
order by
	base.company_id, base.merged_company_id

I can't figure out why it's giving me these results. Instead of the 21/45 pairing I'm looking for, I get a duplicate 22/45.

I feel like I have a fundamental misunderstanding of these hierarchical queries. I'm assuming I don't need a start with here, since I want to get the hierarchy for all records.

答案1

得分: 1

你可以使用 CONNECT_BY_ROOT 获取根 company_id（而不是查询当前级别的 company_id）：

SELECT CONNECT_BY_ROOT company_id AS company_id,
       merged_company_id
FROM   table_name
CONNECT BY PRIOR merged_company_id = company_id

对于示例数据：

CREATE TABLE table_name (company_id, merged_company_id) AS
SELECT 21, 10 FROM DUAL UNION ALL
SELECT 21, 22 FROM DUAL UNION ALL
SELECT 22, 45 FROM DUAL;

输出：

fiddle

SELECT CONNECT_BY_ROOT company_id AS company_id,
       merged_company_id
FROM   table_name
CONNECT BY PRIOR merged_company_id = company_id

CREATE TABLE table_name (company_id, merged_company_id) AS
SELECT 21, 10 FROM DUAL UNION ALL
SELECT 21, 22 FROM DUAL UNION ALL
SELECT 22, 45 FROM DUAL;