Oracle SQL如何实现递归查询获取ID对应的最终真实值？

问题原因 #

你现有CONNECT BY语句的冗余源于遍历逻辑：从真实值节点出发向上关联所有引用它的ID，会返回整条链路的所有中间节点行，导致同一原始ID出现重复/多余结果。同时你使用的正则REGEXP_LIKE(value,'[^0-9]')只要包含非数字就会匹配，不符合「真实值始终以字母开头」的规则，也可能引发误判，建议修改为REGEXP_LIKE(value,'^[A-Za-z]')。

解决方案1：修正CONNECT BY写法 #

调整遍历方向，从所有原始行出发向下追溯，仅保留追溯到最终真实值的结果即可：

with my_data as(
    select '116554226_2' as id, '116554226_1' as value from dual union all
    select '119675285_2' as id, '119675285_1' as value from dual union all
    select '119675285_3' as id, '119675285_2' as value from dual union all
    select '13656777_1' as id, '119675471_1' as value from dual union all
    select '13656777_5001' as id, '119675471_1' as value from dual union all
    select '13656777_2' as id, '13656777_1' as value from dual union all
    select '13656155_1' as id, '13657581_1' as value from dual union all
    select '13657581_2' as id, '13657581_1' as value from dual union all
    select '13657015_1' as id, '13657759_1' as value from dual union all
    select '13657759_2' as id, '13657759_1' as value from dual union all
    select '116554226_1' as id, '471502681_1' as value from dual union all
    select '462721769_1' as id, 'O7X5J' as value from dual union all
    select '471502681_1' as id, 'T3L8L' as value from dual union all
    select '119675471_1' as id, 'T8Q0G' as value from dual union all
    select '119675471_5001' as id, 'T8Q0G' as value from dual union all
    select '116555133_1' as id, 'T9J2Q' as value from dual union all
    select '13657581_1' as id, 'U5H5Z' as value from dual union all
    select '119674049_1' as id, 'Y5G7V' as value from dual union all
    select '13657759_1' as id, 'Z0Y9C' as value from dual union all
    select '119675285_1' as id, 'Z7E0D' as value from dual
)
SELECT 
  CONNECT_BY_ROOT id as id,
  CONNECT_BY_ROOT value as original_value,
  value as final_result
FROM my_data
WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR value = id
START WITH 1=1
ORDER BY id;

核心修正点：

• 遍历方向改为PRIOR value = id：以前一行的value作为下一行的id，实现向下追溯的逻辑
• 用CONNECT_BY_ISLEAF = 1过滤：仅保留追溯到最末端（真实值，没有下一级关联）的结果
• 用CONNECT_BY_ROOT保留原始行的id和value，保证每个原始行只返回1条结果

解决方案2：递归CTE写法（逻辑更清晰，Oracle 11gR2+支持） #

如果你的数据库版本支持递归CTE，用以下写法更易维护，也能避免CONNECT BY的常见逻辑坑：

with my_data as(
    select '116554226_2' as id, '116554226_1' as value from dual union all
    select '119675285_2' as id, '119675285_1' as value from dual union all
    select '119675285_3' as id, '119675285_2' as value from dual union all
    select '13656777_1' as id, '119675471_1' as value from dual union all
    select '13656777_5001' as id, '119675471_1' as value from dual union all
    select '13656777_2' as id, '13656777_1' as value from dual union all
    select '13656155_1' as id, '13657581_1' as value from dual union all
    select '13657581_2' as id, '13657581_1' as value from dual union all
    select '13657015_1' as id, '13657759_1' as value from dual union all
    select '13657759_2' as id, '13657759_1' as value from dual union all
    select '116554226_1' as id, '471502681_1' as value from dual union all
    select '462721769_1' as id, 'O7X5J' as value from dual union all
    select '471502681_1' as id, 'T3L8L' as value from dual union all
    select '119675471_1' as id, 'T8Q0G' as value from dual union all
    select '119675471_5001' as id, 'T8Q0G' as value from dual union all
    select '116555133_1' as id, 'T9J2Q' as value from dual union all
    select '13657581_1' as id, 'U5H5Z' as value from dual union all
    select '119674049_1' as id, 'Y5G7V' as value from dual union all
    select '13657759_1' as id, 'Z0Y9C' as value from dual union all
    select '119675285_1' as id, 'Z7E0D' as value from dual
),
recursive_cte (original_id, original_value, current_value, is_final) as (
    -- 递归起点：所有原始行
    select id as original_id, value as original_value, value as current_value,
           case when regexp_like(value, '^[A-Za-z]') then 1 else 0 end as is_final
    from my_data
    union all
    -- 递归迭代：如果当前值是ID就继续找下一级
    select r.original_id, r.original_value, d.value,
           case when regexp_like(d.value, '^[A-Za-z]') then 1 else 0 end as is_final
    from recursive_cte r
    join my_data d on r.current_value = d.id
    where r.is_final = 0
)
-- 仅保留已经找到最终真实值的结果
select original_id as id, original_value as value, current_value as final_result
from recursive_cte
where is_final = 1
order by original_id;

这种写法的逻辑完全贴合需求，每一步迭代都显式判断是否已经找到真实值，不会出现多余结果，也容易排查异常链路。

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