如何通过无游标的单查询获取指定节点a_b_c的所有父节点？

现有表结构 #

table1（节点信息表） #

table2（父子关系表） #

问题 #

输入节点名称为a_b_c，能否在不使用游标的情况下，通过单条SQL查询获取该节点的所有父节点（包含自身）？预期结果如下：

预期结果 #

解决方案 #

可以借助**递归CTE（公共表表达式）**实现，这是大多数现代SQL数据库（如MySQL 8.0+、PostgreSQL、SQL Server等）都支持的特性，无需游标即可完成层级查询。以下是适配上述需求的SQL代码：

WITH RECURSIVE node_path AS (
    -- 定位目标节点a_b_c
    SELECT 
        t1.Id, 
        t1.Name, 
        t1.Name AS full_name
    FROM table1 t1
    WHERE t1.Name = 'a_b_c'
    
    UNION ALL
    
    -- 递归遍历父节点并处理名称格式
    SELECT 
        t1.Id, 
        CASE 
            -- 如果父节点名称是当前节点全名的前缀（加下划线），截取剩余部分
            WHEN LOCATE(CONCAT(t1.Name, '_'), np.full_name) = 1 
            THEN SUBSTRING(np.full_name, LENGTH(t1.Name) + 2)
            -- 顶层节点直接添加前缀'-'
            ELSE CONCAT('-', t1.Name)
        END AS Name,
        t1.Name AS full_name
    FROM node_path np
    -- 通过关系表关联父节点
    JOIN table2 t2 ON np.Id = t2.childId
    JOIN table1 t1 ON t2.parentId = t1.Id
)
-- 输出最终结果
SELECT Id, Name FROM node_path;

逻辑说明 #

1. 递归CTE的起始部分先找到目标节点a_b_c，同时保存其全名用于后续名称处理
2. 递归部分逐层向上关联父节点：
   • 对于父节点a_b，通过字符串定位截取掉a_b_前缀，得到b_c
   • 对于顶层节点a，因为没有更上层的前缀，直接格式化为-a
3. 最终输出所有层级的节点信息，包含自身及所有父节点

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