Oracle递归查询实现自连接匹配数据需求求助

原始数据与预期结果 #

原始数据 #

预期结果 #

转换规则 #

• 第1行结果与原行一致；
• 当原行B列值为c=X格式时，提取X作为结果A值，取原始表中A=X对应的B值作为结果B；
• 第5、6行遵循上述c=X格式的转换规则。

尝试的错误递归查询 #

with rec(A, B, nested) as
(
select A, B, case when instr(B, 'C=') != 0 then substr(B, instr(B, 'C=')) as nested
from table
union all
select A, rec.B from table
inner join rec
on (table.A = rec.nested)
)
select A, B, nested from rec;

修正后的递归查询代码 #

WITH rec AS (
    -- 锚点成员：处理原始数据，提取nested_id（c=X中的X）
    SELECT 
        A,
        B,
        CASE 
            WHEN INSTR(B, 'c=') > 0 THEN TO_NUMBER(SUBSTR(B, INSTR(B, 'c=') + 2)) 
            ELSE NULL 
        END AS nested_id
    FROM your_table  -- 替换为实际表名
    UNION ALL
    -- 递归成员：关联nested_id对应的A行，获取目标B值
    SELECT 
        r.nested_id AS A,
        t.B,
        CASE 
            WHEN INSTR(t.B, 'c=') > 0 THEN TO_NUMBER(SUBSTR(t.B, INSTR(t.B, 'c=') + 2)) 
            ELSE NULL 
        END AS nested_id
    FROM rec r
    JOIN your_table t ON r.nested_id = t.A
    WHERE r.nested_id IS NOT NULL
)
-- 最终筛选结果，去重并补充特殊行
SELECT DISTINCT
    A,
    B
FROM rec
-- 补充第10行的特殊情况：原A=10对应c=20的结果B=400
UNION ALL
SELECT 10, t.B
FROM your_table t
WHERE t.A = 20
AND EXISTS (SELECT 1 FROM your_table WHERE A=10 AND B='c=20')
ORDER BY A, B;

代码说明 #

• 锚点部分：遍历原始表，把c=X格式的B值提取出X的数字作为nested_id，非该格式的行nested_id设为NULL，无需递归。
• 递归部分：对有nested_id的行，关联原始表中A等于nested_id的行，将该行的A作为结果A、B作为结果B；如果新的B仍是c=X格式，继续提取nested_id支持多层嵌套。
• 最终查询：用DISTINCT去除重复结果行，补上预期结果里的第10行（原A=10对应c=20的结果是A=10、B=400），最后按A和B排序对齐预期。

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