Oracle 11g层级SQL查询修改：获取父级英文名称字段

数据库结构 #

• my_item_lib表（存储物品多语言名称）：

drop table my_item_lib;

create table my_item_lib
(
    id_item number,
    lib_fr  varchar2(100),
    lib_en  varchar2(100)
);

insert into my_item_lib values (1, '1_fr', '1_en');
insert into my_item_lib values (2, '2_fr', '2_en');
insert into my_item_lib values (3, '3_fr', '3_en');
insert into my_item_lib values (4, '4_fr', '4_en');
insert into my_item_lib values (5, '5_fr', '5_en');
insert into my_item_lib values (6, '6_fr', '6_en');
insert into my_item_lib values (7, '7_fr', '7_en');
insert into my_item_lib values (8, '8_fr', '8_en');
insert into my_item_lib values (9, '9_fr', '9_en');
insert into my_item_lib values (10, '10_fr', '10_en');

• my_item_hierarchy表（存储物品层级关系）：

drop table my_item_hierarchy;

create table my_item_hierarchy
(
    id_item     number,
    id_item_sup number,
    ordre       number
);

insert into my_item_hierarchy values (1, null, 1);
insert into my_item_hierarchy values (2, 1, 2);
insert into my_item_hierarchy values (3, 2, 3);
insert into my_item_hierarchy values (4, 3, 4);
insert into my_item_hierarchy values (5, 3, 5);
insert into my_item_hierarchy values (6, 4, 6);
insert into my_item_hierarchy values (7, 4, 7);
insert into my_item_hierarchy values (8, 2, 8);
insert into my_item_hierarchy values (9, 1, 9);
insert into my_item_hierarchy values (10, 9, 10);

原查询代码 #

用于获取每个物品的父级、祖父级、曾祖父级ID：

select *
from
(
    with v00 as
    (
        select  
        a.id_item       
        , lib.lib_fr
        , lib.lib_en
        -- --
        , a.id_item_sup
        , lib_sup.lib_fr lib_fr_sup
        , lib_sup.lib_en lib_en_sup
        from my_item_hierarchy a
        , my_item_lib lib
        , my_item_lib lib_sup
        where 1 = 1
        and a.id_item = lib.id_item
        and a.id_item_sup = lib_sup.id_item(+)
    ), h as
    (
        select 
        connect_by_root id_item id_item
        , id_item_sup
        , level lvl
        from v00
        where 1 = 1
        connect by 
        id_item = prior id_item_sup 
        and level <= 3
    )
    select id_item
    , id_item_sup1, id_item_sup2, id_item_sup3
    from h
    pivot (max(id_item_sup) for lvl in (1 as id_item_sup1, 2 as id_item_sup2, 3 as id_item_sup3))
    order by 1
)
;

需求说明 #

修改原查询，使其输出同时包含对应父级的英文名称字段：LIB_EN_SUP1（父级英文名称）、LIB_EN_SUP2（祖父级英文名称）、LIB_EN_SUP3（曾祖父级英文名称），期望输出如下：

ID_ITEM ID_ITEM_SUP1 ID_ITEM_SUP2 ID_ITEM_SUP3 LIB_EN_SUP1     LIB_EN_SUP2     LIB_EN_SUP3
---------- ------------ ------------ ------------ --------------- --------------- ---------------
         1                                        
         2            1                           1_en
         3            2            1              2_en            1_en           
         4            3            2            1 3_en            2_en             1_en
         5            3            2            1 3_en            2_en             1_en
         6            4            3            2 4_en            3_en             2_en
         7            4            3            2 4_en            3_en             2_en
         8            2            1              2_en            1_en   
         9            1                           1_en
        10            9            1              9_en            1_en

修改方案 #

适配Oracle 11.2.0.3版本的修改后SQL：

select *
from
(
    with v00 as
    (
        select  
        a.id_item       
        , lib.lib_fr
        , lib.lib_en
        , a.id_item_sup
        , lib_sup.lib_en lib_en_sup
        from my_item_hierarchy a
        join my_item_lib lib on a.id_item = lib.id_item
        left join my_item_lib lib_sup on a.id_item_sup = lib_sup.id_item
    ), h as
    (
        select 
        connect_by_root id_item id_item
        , id_item_sup
        , lib_en_sup
        , level lvl
        from v00
        connect by id_item = prior id_item_sup 
        and level <= 3
    )
    select 
        id_item
        , id_item_sup1, id_item_sup2, id_item_sup3
        , lib_en_sup1, lib_en_sup2, lib_en_sup3
    from h
    pivot (
        max(id_item_sup) as id_item_sup, max(lib_en_sup) as lib_en_sup
        for lvl in (1 as sup1, 2 as sup2, 3 as sup3)
    )
    order by id_item
)
;

修改要点 #

1. CTE v00优化：将隐式连接改为显式join语法，仅保留需要的父级英文名称字段lib_en_sup。
2. 层级查询扩展：在层级遍历中同步获取lib_en_sup字段，确保每一层级的父ID和对应英文名称都被捕获。
3. 多字段Pivot处理：利用Oracle 11g支持的多字段聚合Pivot语法，同时对id_item_sup和lib_en_sup进行分组聚合，通过别名映射到目标输出字段。
4. 输出字段调整：在最终查询中列出所有需要的ID和英文名称字段，保证输出结构与需求一致。

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