Oracle多表层级查询问题：实现LEVEL伪列对应不同表层级

问题分析 #

原查询的核心问题在于：

• 层级关系字段拆分（parent1Id/parent2Id）导致Oracle无法识别统一的父子链条
• 未指定START WITH起始节点，Oracle将所有行视为顶级节点，因此LEVEL始终为1
• CONNECT BY条件逻辑颠倒，未使用PRIOR关键字正确关联父子节点
• 第三个UNION分支的src值类型不统一（数字3 vs 字符串'T1'/'T2'）

正确查询方案 #

通过统一父子关系字段、明确起始节点和正确关联层级，可实现预期的LEVEL分配：

WITH hierarchy_data AS (
    -- 顶级节点：Table1（州），对应LEVEL 1
    SELECT 
        'T1' AS src,
        id,
        NULL AS parent_id,
        t1name AS name,
        description
    FROM table1
    UNION ALL
    -- 二级节点：Table2（城市），父节点为Table1的ID，对应LEVEL 2
    SELECT 
        'T2' AS src,
        id,
        table1ID AS parent_id,
        t2name AS name,
        description
    FROM table2
    UNION ALL
    -- 三级节点：Table3（邮编），父节点为Table2的ID，对应LEVEL 3
    SELECT 
        'T3' AS src,
        id,
        table2ID AS parent_id,
        t3name AS name,
        description
    FROM table3
)
SELECT 
    LEVEL,
    src,
    id,
    parent_id,
    name,
    description,
    CONNECT_BY_ISLEAF
FROM hierarchy_data
START WITH parent_id IS NULL  -- 从顶级节点（州）开始遍历
CONNECT BY PRIOR id = parent_id;  -- 关联父节点ID与当前节点的parent_id

关键说明 #

• 使用UNION ALL替代UNION：避免不必要的去重操作，提升查询效率（若需去重可保留UNION）
• 统一parent_id字段：让Oracle清晰识别层级链条，Table1的parent_id为NULL（顶级），Table2关联Table1的ID，Table3关联Table2的ID
• START WITH parent_id IS NULL：指定从顶级节点（Table1）开始遍历
• CONNECT BY PRIOR id = parent_id：PRIOR关键字表示上一层级的节点，确保父节点的ID等于当前节点的parent_id，正确构建层级关系

执行后，Table1的行LEVEL为1，Table2为2，Table3为3；CONNECT_BY_ISLEAF仅在Table3的行显示为1（无下级节点），其余为0。

可选表结构优化建议 #

若允许调整表结构，可考虑：

• 新增统一的层级表（如location_hierarchy），包含id、parent_id、type（州/城市/邮编）、以及各类型的属性字段，简化层级查询
• 为三个表新增统一的parent_id字段（Table1的parent_id为NULL，Table2的parent_id关联Table1，Table3关联Table2），避免每次查询都需要UNION操作

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