PostgreSQL递归查询所有节点的祖先节点

假设你的表名为tree_nodes（实际表名不同时替换即可），表结构如下：

可以使用**递归CTE（Common Table Expression）**实现需求，SQL语句如下：

WITH RECURSIVE node_ancestors AS (
    -- 基础查询：获取非根节点的直接父节点
    SELECT 
        node_id, 
        parent_id AS ancestor_id
    FROM tree_nodes
    WHERE parent_id IS NOT NULL

    UNION ALL

    -- 递归查询：向上遍历获取所有层级的祖先节点
    SELECT 
        na.node_id, 
        tn.parent_id AS ancestor_id
    FROM node_ancestors na
    JOIN tree_nodes tn ON na.ancestor_id = tn.node_id
    WHERE tn.parent_id IS NOT NULL
)
SELECT node_id, ancestor_id
FROM node_ancestors
ORDER BY node_id, ancestor_id DESC;

代码说明： #

• 基础部分：筛选出所有非根节点，先获取它们的直接父节点作为第一层祖先。
• 递归部分：以已找到的祖先节点为起点，继续向上关联其父节点，直到遍历到根节点（根节点无父节点，tn.parent_id IS NOT NULL用于终止递归）。
• 最终输出：取出所有结果并按node_id、ancestor_id降序排列，与期望输出一致。

执行后得到的结果：

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