如何通过单SQL操作从父子表获取节点到根节点的完整路径？

要实现单次数据库操作获取所有（或指定）节点到根节点的完整路径，核心是用递归查询——这是关系型数据库处理层级结构数据的标准方案，能避免多次循环查询的开销。

下面针对不同主流数据库给出具体实现：

1. MySQL 8.0+/PostgreSQL/SQL Server（支持WITH RECURSIVE） #

这类数据库支持递归CTE（公共表表达式），可以逐层拼接路径：

WITH RECURSIVE node_paths AS (
    -- 基础部分：先获取所有根节点（parent_id为NULL），路径初始化为自身名称
    SELECT 
        id, 
        name, 
        CAST(name AS VARCHAR(1000)) AS path  -- 注意根据实际数据长度调整类型长度
    FROM your_table
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归部分：关联子节点与父节点，拼接路径
    SELECT 
        child.id, 
        child.name, 
        CONCAT(parent.path, ' / ', child.name) AS path
    FROM your_table child
    JOIN node_paths parent ON child.parent_id = parent.id
)
-- 这里可以查询所有节点的路径，或者加WHERE id = 5指定单个节点
SELECT id, name, path FROM node_paths;

执行后就能得到所有节点的完整路径，比如id=5的path就是id1 / id2 / abc，id=4对应id1 / id2 / aa3 / bb4。

2. Oracle数据库 #

Oracle有专门的层级查询语法，结合SYS_CONNECT_BY_PATH函数快速生成路径：

SELECT 
    id,
    name,
    -- 去掉路径开头多余的' / '分隔符
    LTRIM(SYS_CONNECT_BY_PATH(name, ' / '), ' / ') AS path
FROM your_table
-- 指定根节点的起始条件
START WITH parent_id IS NULL
-- 定义层级关联关系：父节点id等于当前节点的parent_id
CONNECT BY PRIOR id = parent_id
-- 可选：按节点顺序排序
ORDER SIBLINGS BY id;

关键说明 #

• 两种方案都是单次SQL执行，不需要在应用层循环查询数据库，性能远优于Python循环调用的方式；
• 如果只需要单个节点的路径，在最终的SELECT语句后加WHERE id = 目标id即可，比如WHERE id = 4；
• 注意调整字符串类型的长度（比如MySQL里的VARCHAR长度），避免路径过长导致截断。

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