Oracle层级表构建问题：现有机构数据需按编码规则重构

嘿，我刚好碰到过类似的Oracle层级表重构需求，给你一套实用的解决方案！

从你给出的表格和层级规则来看，原表是扁平结构，仅通过编码长度和前缀隐含了父子关系，我们需要把这种隐含关系显性化，重构出带明确层级和父节点关联的表格。

方案1：分层级关联（适合固定3层场景） #

这种方式直观易懂，直接针对每个层级提取对应的父节点编码：

-- 生成重构后的层级数据
SELECT 
    Agency_CODE,
    NULL AS Parent_Agency_Code,
    Agency_Name,
    1 AS Level
FROM your_table
WHERE Code_length = 1

UNION ALL

SELECT 
    t2.Agency_CODE,
    SUBSTR(t2.Agency_CODE, 1, 1) AS Parent_Agency_Code,
    t2.Agency_Name,
    2 AS Level
FROM your_table t2
WHERE t2.Code_length = 2

UNION ALL

SELECT 
    t3.Agency_CODE,
    SUBSTR(t3.Agency_CODE, 1, 2) AS Parent_Agency_Code,
    t3.Agency_Name,
    3 AS Level
FROM your_table t3
WHERE t3.Code_length = 3

ORDER BY Level, Agency_CODE;

执行后会得到这样的结构化结果：

如果需要持久化这个重构后的表，可以用以下语句：

CREATE TABLE restructured_agency AS
SELECT 
    Agency_CODE,
    CASE 
        WHEN Code_length = 1 THEN NULL
        WHEN Code_length = 2 THEN SUBSTR(Agency_CODE, 1, 1)
        WHEN Code_length = 3 THEN SUBSTR(Agency_CODE, 1, 2)
    END AS Parent_Agency_Code,
    Agency_Name,
    Code_length AS Level
FROM your_table
ORDER BY Level, Agency_CODE;

方案2：Oracle层级查询（适合可扩展层级场景） #

如果以后可能增加更多层级（比如4位编码的Level4节点），用Oracle原生的CONNECT BY语法会更灵活，无需修改SQL结构：

SELECT 
    Agency_CODE,
    PRIOR Agency_CODE AS Parent_Agency_Code,
    Agency_Name,
    LEVEL AS Level
FROM your_table
START WITH Code_length = 1 -- 指定Level1根节点
CONNECT BY PRIOR Agency_CODE = SUBSTR(Agency_CODE, 1, LENGTH(PRIOR Agency_CODE)) -- 父子节点匹配规则
ORDER SIBLINGS BY Agency_CODE;

这个语句会自动根据编码前缀匹配父子关系，哪怕后续新增更长的编码，只要前缀规则不变，就能直接识别层级。

内容的提问来源于stack exchange，提问作者Mohamed A M-Hassan