Oracle中向上遍历层级的简单递归查询实现求助

我完全懂这种感觉——递归查询的逻辑绕来绕去，尤其是向上遍历层级的时候，总觉得不知道怎么“终止”或者怎么一步步往上找。别担心，咱们用**递归CTE（Common Table Expression）**就能轻松搞定这个需求，我给你拆解清楚每一步。

首先，先明确你的表结构（你已经给出了）：

CREATE TABLE family_tree ( 
    child varchar(10), 
    parent varchar(10) 
);

先插入点测试数据方便演示：

INSERT INTO family_tree (child, parent) VALUES
('张三', '李四'),
('李四', '王五'),
('王五', '赵六'),
('赵六', NULL); -- 赵六是根节点，没有父节点

核心实现：递归CTE #

递归CTE分为两部分：锚点成员（起始查询）和递归成员（重复执行的关联逻辑），咱们就用它来从目标节点一路往上追到根节点。

基础版本：获取所有父节点 #

假设你要查询张三的所有祖先（包括根节点赵六），SQL代码如下：

WITH RECURSIVE ancestor_tree AS (
    -- 1. 锚点成员：从目标节点开始
    SELECT 
        child, 
        parent,
        1 AS depth -- 可选字段，记录当前节点到目标节点的层级深度
    FROM family_tree
    WHERE child = '张三' -- 替换成你要查询的子节点
    
    UNION ALL
    
    -- 2. 递归成员：向上遍历父节点
    SELECT 
        ft.child, 
        ft.parent,
        at.depth + 1 AS depth
    FROM family_tree ft
    -- 把上一轮结果里的parent作为当前的child，继续找它的父节点
    JOIN ancestor_tree at ON ft.child = at.parent
    -- 当parent为NULL时停止（根节点）
    WHERE ft.parent IS NOT NULL
)
-- 最终提取所有祖先节点
SELECT parent AS ancestor, depth
FROM ancestor_tree
WHERE parent IS NOT NULL; -- 如果不想包含NULL（根节点的父节点）可以加这个条件

执行这个查询后，你会得到：

进阶版本：生成完整祖先路径 #

如果你想看到从目标节点到根节点的完整路径，可以修改递归CTE来拼接路径：

WITH RECURSIVE ancestor_tree AS (
    SELECT 
        child, 
        parent,
        1 AS depth,
        CAST(child AS VARCHAR(100)) AS path -- 初始路径是目标节点本身
    FROM family_tree
    WHERE child = '张三'
    
    UNION ALL
    
    SELECT 
        ft.child, 
        ft.parent,
        at.depth + 1 AS depth,
        CONCAT(at.path, ' ← ', ft.child) AS path -- 拼接父节点，形成路径
    FROM family_tree ft
    JOIN ancestor_tree at ON ft.child = at.parent
)
SELECT 
    parent AS ancestor,
    depth,
    path
FROM ancestor_tree
WHERE parent IS NOT NULL;

执行后会得到：

注意事项 #

• 处理循环数据：如果你的表中不小心出现了循环（比如A的父是B，B的父是A），递归会无限执行。这时候可以加递归深度限制：
  • SQL Server：在查询末尾加OPTION (MAXRECURSION 100)（数字是最大递归次数）
  • PostgreSQL：可以用LIMIT或者设置max_recursion_depth参数
• 根节点判断：如果根节点的parent是NULL，递归成员里的WHERE ft.parent IS NOT NULL会自动终止递归；如果你的根节点用其他值标记（比如空字符串），记得修改这个条件。

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