PostgreSQL使用WITH RECURSIVE查询生成项目父子层级有序路径名

原有脚本问题说明 #

• 字段名与实际表结构不匹配：脚本中使用的id/parent_id和实际表的projectID/parentID字段不一致
• 递归逻辑仅做了层级标记和ID序列拼接，没有同步拼接项目名称生成目标路径
• 锚点仅从根节点启动遍历，没有适配单节点向上追溯全路径的场景

全树遍历查询脚本（从根节点返回所有节点的完整路径） #

以下脚本兼容MySQL 8.0+、PostgreSQL等支持标准递归CTE的数据库：

WITH RECURSIVE project_tree AS (
    -- 锚点：查询所有根节点（无父级的顶层项目）
    SELECT 
        projectID,
        name,
        parentID,
        description,
        name AS Path,
        CAST(projectID AS CHAR(200)) AS order_sequence
    FROM projects
    WHERE parentID IS NULL

    UNION ALL

    -- 递归：逐层关联子节点，拼接路径
    SELECT 
        child.projectID,
        child.name,
        child.parentID,
        child.description,
        CONCAT(tv.Path, '/', child.name) AS Path,
        CAST(CONCAT(tv.order_sequence, '_', child.projectID) AS CHAR(200)) AS order_sequence
    FROM projects child
    JOIN project_tree tv
      ON child.parentID = tv.projectID
)
SELECT projectID, name, parentID, description, Path
FROM project_tree
ORDER BY order_sequence;

运行后会按照树的层级顺序返回所有节点，Path字段为从根节点到当前节点的/分隔名称路径。

单节点向上追溯路径脚本 #

如果只需要查询某一个指定项目的完整祖先路径，可以调整递归方向为从当前节点向上查找父节点：

WITH RECURSIVE project_path AS (
    -- 锚点：指定要查询的目标项目ID，例如下方查询projectID=1的路径
    SELECT 
        projectID,
        name,
        parentID,
        description,
        name AS Path,
        CAST(projectID AS CHAR(200)) AS order_sequence
    FROM projects
    WHERE projectID = 1

    UNION ALL

    -- 递归：逐层关联父节点，反向拼接路径
    SELECT 
        parent.projectID,
        parent.name,
        parent.parentID,
        parent.description,
        CONCAT(parent.name, '/', tv.Path) AS Path,
        CAST(CONCAT(parent.projectID, '_', tv.order_sequence) AS CHAR(200)) AS order_sequence
    FROM projects parent
    JOIN project_path tv
      ON parent.projectID = tv.parentID
)
SELECT projectID, name, parentID, description, Path
FROM project_path
ORDER BY LENGTH(Path) DESC
LIMIT 1;

注意事项 #

• 不同数据库语法存在差异：SQL Server中可将CONCAT替换为+做字符串拼接；Oracle 11g及更早版本不支持递归CTE，需要使用CONNECT BY层级查询语法实现
• order_sequence的字符长度请根据实际项目最大层级调整，避免层级过深导致字符串截断
• 给出的期望结果示例中，projectID=2、projectID=3的parentID取值和前面的表结构定义不一致，实际查询结果以表中存储的父子关联关系为准

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