求助：如何构建Hierarchical Table实现编码层级关联？

可以使用Oracle的递归CTE（Common Table Expression）遍历层级关系，再通过条件聚合转换为目标格式的宽表。以下是完整SQL代码：

WITH data(left_code, left_category, right_code, right_category) as (
    SELECT '21BEMVXP040150FIS4', 'A', '21CYMVXP040152VFO4', 'B' FROM DUAL UNION ALL
    SELECT '21CYMVXP040152VFO4', 'B', '23FRDDS2NCNF1LOBR4', 'C' FROM DUAL UNION ALL
    SELECT '22NLDDS2ACNF3MQJC4', 'B', '21BEMVXP040150FIS9', 'A' FROM DUAL UNION ALL
    SELECT '21BEMVXP040150FIS9', 'A', '23FRDDS2NCNF1LOBR9', 'C' FROM DUAL UNION ALL
    SELECT '21DEMVXP040222UJK5', 'B', '23FRDDS4NCNF1LOBR4', 'C' FROM DUAL
),
hierarchy AS (
    -- 锚点：筛选所有根节点（未被其他节点指向的left_code）
    SELECT 
        left_code AS code,
        left_category AS category,
        right_code AS next_code,
        right_category AS next_category,
        1 AS level_num
    FROM data
    WHERE left_code NOT IN (SELECT right_code FROM data)
    UNION ALL
    -- 递归遍历下一层级，最多到3层
    SELECT 
        h.code,
        h.category,
        d.right_code AS next_code,
        d.right_category AS next_category,
        h.level_num + 1 AS level_num
    FROM hierarchy h
    JOIN data d ON h.next_code = d.left_code
    WHERE h.level_num < 3
),
-- 将层级数据转换为目标宽表结构
pivoted AS (
    SELECT
        code AS CODE_1,
        category AS CATEGORY_1,
        MAX(CASE WHEN level_num = 1 THEN next_code END) AS CODE_2,
        MAX(CASE WHEN level_num = 1 THEN next_category END) AS CATEGORY_2,
        MAX(CASE WHEN level_num = 2 THEN next_code END) AS CODE_3,
        MAX(CASE WHEN level_num = 2 THEN next_category END) AS CATEGORY_3
    FROM hierarchy
    GROUP BY code, category
)
SELECT * FROM pivoted;

代码说明 #

1. data CTE：定义原始数据集，修正了原SQL末尾多余的逗号语法错误。
2. hierarchy CTE：
   • 锚点成员：筛选出所有根节点（即left_code从未出现在其他记录的right_code中），标记层级为1，并记录下一级节点信息。
   • 递归成员：通过关联next_code与下一条记录的left_code，向下遍历层级，直到层级达到3（因为目标表最多3层）。
3. pivoted CTE：使用条件聚合，将不同层级的节点信息转换为目标表的列结构，没有对应层级的节点自动填充NULL。

补充说明 #

若需要包含示例中的第四条记录（22NLDDS2ACNF3MQJC2→22FRDDS2NCNF1LOBR0），只需在data CTE中添加对应的UNION ALL语句即可：

SELECT '22NLDDS2ACNF3MQJC2', 'A', '22FRDDS2NCNF1LOBR0', 'C' FROM DUAL

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