PostgreSQL：使用WITH RECURSIVE构建条目及其父项列表

嘿，我来帮你搞定这个递归分类的查询问题！首先，你的category表是典型的自关联树形结构，PostgreSQL的WITH RECURSIVE正好用来处理这种层级数据。

先提个小细节：你插入的(6,7,'scary1')这条记录的parent是7，但表里根本没有id=7的分类，所以递归的时候这条记录没法找到父节点，只会显示自身，后面的查询结果里你会看到这一点。

接下来直接上实用的查询，我分几种常见需求说明：

1. 获取每个分类的完整路径（从根节点到当前分类） #

这个查询会给每个分类生成一条从顶级父类（比如'animal'）到自身的路径，同时显示层级深度：

WITH RECURSIVE category_hierarchy AS (
    -- 非递归部分：先把所有分类作为初始节点，路径初始化为自身，深度0
    SELECT
        id,
        parent,
        name,
        ARRAY[name] AS full_path,
        0 AS depth
    FROM category

    UNION ALL

    -- 递归部分：连接父节点，把父节点的路径和当前分类名拼接，深度+1
    SELECT
        child.id,
        parent.parent,
        child.name,
        parent.full_path || child.name,
        parent.depth + 1
    FROM category child
    JOIN category_hierarchy parent ON child.parent = parent.id
)
-- 最终查询：按分类ID和深度排序，清晰展示层级
SELECT id, name, full_path, depth
FROM category_hierarchy
ORDER BY id, depth DESC;

执行后你会看到：

• id=4（siamese）的full_path是['animal', 'cat', 'siamese']，depth=2
• id=6（scary1）的full_path只有['scary1']，因为找不到父节点

2. 获取每个分类的所有父节点列表（从直接父到根） #

如果只需要每个分类的父节点集合（不含自身），可以调整递归逻辑：

WITH RECURSIVE parent_list AS (
    -- 非递归部分：先获取每个分类的直接父节点
    SELECT
        child.id AS child_id,
        child.name AS child_name,
        parent.id AS parent_id,
        parent.name AS parent_name,
        ARRAY[parent.name] AS parents
    FROM category child
    LEFT JOIN category parent ON child.parent = parent.id

    UNION ALL

    -- 递归部分：继续向上找父节点的父节点
    SELECT
        pl.child_id,
        pl.child_name,
        grandparent.id,
        grandparent.name,
        pl.parents || grandparent.name
    FROM parent_list pl
    JOIN category grandparent ON pl.parent_id = grandparent.id
)
-- 最终查询：按子分类ID分组，取最长的父节点列表（即完整的父链）
SELECT
    child_id,
    child_name,
    parents
FROM parent_list
WHERE parent_id IS NULL -- 找到根节点的父节点（NULL），此时的parents就是完整父链
OR child_id = 6 -- 单独处理那个没有父节点的分类
ORDER BY child_id;

这个查询会返回：

• id=4的parents是['cat', 'animal']
• id=6的parents为NULL（因为找不到父节点）

3. 树形结构展示（缩进区分层级） #

如果想直观展示树形结构，用缩进区分层级，可以这样写：

WITH RECURSIVE category_tree AS (
    SELECT
        id,
        parent,
        name,
        0 AS depth
    FROM category
    WHERE parent IS NULL -- 从根节点开始遍历

    UNION ALL

    SELECT
        child.id,
        child.parent,
        child.name,
        parent.depth + 1
    FROM category child
    JOIN category_tree parent ON child.parent = parent.id
)
SELECT
    REPEAT('  ', depth) || name AS tree_view,
    id,
    parent
FROM category_tree
ORDER BY depth, name;

执行后会看到清晰的缩进结构：

animal
  dog
  cat
    siamese
    persian
scary1 -- 因找不到父节点，单独显示

最后再提醒下scary1的记录，它的parent是不存在的id=7，建议要么补上id=7的分类，要么把它的parent改成存在的ID（比如1），这样递归才能正常遍历它的父链。

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