PostgreSQL树形表查询：获取指定节点的所有最低层级叶子子节点

直接使用PostgreSQL原生递归公共表表达式（WITH RECURSIVE）实现，无需提前感知层级深度，语句如下：

WITH RECURSIVE descendants AS (
    -- 锚点：指定要查询的根节点
    SELECT ikey, description, parent
    FROM items
    WHERE ikey = 4 -- 此处替换为目标节点的ikey即可
    UNION ALL
    -- 递归遍历所有子节点
    SELECT i.ikey, i.description, i.parent
    FROM items i
    INNER JOIN descendants d ON i.parent = d.ikey
)
-- 筛选所有没有子节点的叶子节点
SELECT *
FROM descendants d
WHERE NOT EXISTS (
    SELECT 1 FROM items i WHERE i.parent = d.ikey
);

逻辑说明 #

• 递归CTE首先定位到你指定的起始节点，再逐层向下遍历所有后代节点，直到没有新的子节点匹配时自动终止，支持任意深度的树形结构。
• 最终通过NOT EXISTS过滤规则：如果某个节点没有出现在任意记录的parent字段中，说明该节点没有子节点，就是要找的叶子节点。

效果验证 #

针对你提供的示例数据，如果查询ikey=4的节点：

• 若你示例中编号10、11的parent为8是笔误，那么查询结果和你给出的预期完全一致，返回6、7、8三条记录。
• 如果10、11确实是8的子节点，那么8会被自动排除，返回6、7两条记录，符合叶子节点的定义。

优化建议 #

如果表数据量较大，建议给parent字段建立B树索引，可显著降低递归关联的耗时：

CREATE INDEX idx_items_parent ON items(parent);

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