Oracle层级查询返回重复数据而非传递性父子对问题

问题描述 #

现有合并公司表merged_companies，数据如下：

需要获取传递性合并关系：即被间接合并到顶层公司的记录也需关联到顶层公司（比如45通过22间接合并到21，需生成21/45的配对），但原层级查询未得到预期结果，反而出现重复记录。

原查询的错误原因 #

1. 层级方向搞反：原CONNECT BY prior company_id = base.merged_company_id的逻辑是从子公司往父公司追溯，而非从顶层公司向下遍历所有被合并的子公司。
2. 缺少START WITH子句：未指定层级遍历的起始点，Oracle会把每一行都作为起始节点，导致生成冗余路径和重复记录。
3. 未保留顶层公司ID：没有使用CONNECT_BY_ROOT函数绑定最终结果到顶层公司，无法生成21/45这类间接关联的配对。
4. 不必要的过滤条件：CTE中限制company_id in ('21','22')会截断层级遍历路径，影响间接关联的查询。

正确解法 #

使用CONNECT_BY_ROOT保留顶层公司ID，调整层级遍历的方向和起始点：

SELECT
    CONNECT_BY_ROOT company_id AS top_company_id,
    merged_company_id AS merged_to_top_id
FROM
    merged_companies
-- 指定顶层公司：这里以21为例，若要所有顶层公司可替换为下方注释的条件
START WITH company_id = '21'
-- START WITH company_id NOT IN (SELECT merged_company_id FROM merged_companies WHERE merged_company_id IS NOT NULL)
CONNECT BY PRIOR merged_company_id = company_id
-- 若存在循环合并的情况，添加NOCYCLE防止无限递归
-- CONNECT BY NOCYCLE PRIOR merged_company_id = company_id
ORDER BY top_company_id, merged_to_top_id;

查询结果说明 #

执行后会返回所有直接/间接合并到顶层公司的关联记录：

关键逻辑解释 #

• START WITH company_id = '21'：指定从接收合并的顶层公司21开始遍历
• CONNECT BY PRIOR merged_company_id = company_id：定义层级关系——上一层的被合并公司（如21的merged_company_id=22）是当前层的接收合并公司（22），从而递归找到22的被合并公司45
• CONNECT_BY_ROOT company_id：无论递归到哪一层，始终返回最顶层的公司ID（21），确保间接合并的公司也能关联到顶层公司

内容的提问来源于stack exchange，提问作者Jamie