如何从t_hierarchy层级表获取id_type为0节点的同类型顶级祖先

问题背景 #

现有层级表t_hierarchy，表结构及初始化数据如下：

create table t_hierarchy
(
    id          number  ,
    id_sup      number  ,
    id_type number
);
insert into t_hierarchy values (1, null, 1);
insert into t_hierarchy values (2, 1, 1);

insert into t_hierarchy values (3, 2, 0);
insert into t_hierarchy values (4, 3, 0);
insert into t_hierarchy values (5, 4, 0);
-- ----
insert into t_hierarchy values (6, 2, 1);

insert into t_hierarchy values (7, 6, 0);
insert into t_hierarchy values (8, 7, 0);
-- ----
insert into t_hierarchy values (9, 2, 1);

insert into t_hierarchy values (10, 9, 0);
insert into t_hierarchy values (11, 10, 0);
-- ----
insert into t_hierarchy values (12, 2, 1);

insert into t_hierarchy values (13, 12, 0);
insert into t_hierarchy values (14, 13, 0);
insert into t_hierarchy values (15, 14, 0);

insert into t_hierarchy values (16, 12, 0);
insert into t_hierarchy values (17, 16, 0);
insert into t_hierarchy values (18, 17, 0);

需求：针对每个id_type = 0的节点，获取其所在分支中的顶级同类型祖先——即向上追溯时，该分支中第一个出现的id_type = 0的节点（例如节点3、4、5的顶级同类型祖先均为3）；id_type ≠ 0的节点无需填充该字段。

期望输出：

id  id_sup  id_type last_ancestor
--  ------  ------- -------------
1           1       
2   1       1   
3   2       0       3
4   3       0       3
5   4       0       3
6   2       1       
7   6       0       7
8   7       0       7
9   2       1       
10  9       0       10
11  10      0       10
12  2       1       
13  12      0       13
14  13      0       13
15  14      0       13
16  12      0       16
17  16      0       16
18  17      0       16

解决方案 #

使用Oracle递归CTE（公共表表达式）实现，核心逻辑是：

• 锚点成员：先标记出父节点不是id_type=0的id_type=0节点（这些节点本身就是所在分支的顶级同类型节点）
• 递归成员：对于未标记的id_type=0节点，继承其父节点的顶级同类型祖先

完整SQL如下：

WITH hierarchy_with_ancestor AS (
    -- 锚点：初始化顶级同类型节点及非目标类型节点
    SELECT 
        id,
        id_sup,
        id_type,
        CASE 
            WHEN id_type = 0 AND (id_sup IS NULL OR (SELECT id_type FROM t_hierarchy WHERE id = h.id_sup) != 0) 
            THEN id 
            ELSE NULL 
        END AS last_ancestor
    FROM t_hierarchy h
    UNION ALL
    -- 递归：传递父节点的顶级同类型祖先
    SELECT 
        h.id,
        h.id_sup,
        h.id_type,
        ha.last_ancestor
    FROM t_hierarchy h
    JOIN hierarchy_with_ancestor ha ON h.id_sup = ha.id
    WHERE h.id_type = 0 AND h.last_ancestor IS NULL
)
-- 最终查询，按id排序输出
SELECT 
    id,
    id_sup,
    id_type,
    last_ancestor
FROM hierarchy_with_ancestor
ORDER BY id;

说明 #

• 递归CTE会逐层向上追溯id_type=0节点的父节点，直到找到已标记的顶级同类型节点
• 非id_type=0的节点last_ancestor字段保持为NULL，符合输出要求
• 最终结果按id排序，与期望输出顺序一致

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