使用SQL递归获取树形层级：关联查询无结果问题求助

预期输出表（dist为节点间距离） #

我单独测试部分逻辑正常，但运行整段递归SQL时无数据返回，我的代码如下：

with data_1 as(select child_id  as id ,parent_id as pa_id from parent),
main as(SELECT d1.id as id 
              ,d2.pa_id as pa_id
              ,0 as dist
       FROM data_1 d1 JOIN data_1 d2 ON d1.id = d2.pa_id
       UNION ALL 
       SELECT d3.id as id 
             ,d4.pa_id as pa_id
             ,1+dist as dist
       FROM data_1 d3 JOIN main d4 ON d4.id = d3.id


)

select distinct id,pa_id,dist from main  
--option (maxrecursion 0)

问题分析 #

你的递归CTE存在三个核心错误：

1. 初始查询逻辑错误：d1.id = d2.pa_id的关联条件会过滤掉根节点（比如id=1这类没有父节点的节点），而且完全没包含每个节点到自身的dist=0记录，这是递归的基础起点。
2. 递归关联逻辑错误：d4.id = d3.id是无意义的自连接，无法沿着节点层级遍历，导致递归无法生成新的记录，自然没有输出。
3. 字段映射错误：最终输出的id、pa_id和预期的parent_id、child_id字段名、顺序都不匹配。

修正后的SQL代码 #

WITH RECURSIVE node_paths AS (
    -- 初始步骤：每个节点到自身的距离为0，确保所有节点都有起点
    SELECT 
        child_id AS parent_id,
        child_id AS child_id,
        0 AS dist
    FROM parent
    UNION ALL
    -- 递归步骤：沿着父节点→子节点的层级遍历，累加距离
    SELECT 
        np.parent_id,
        p.child_id,
        np.dist + 1 AS dist
    FROM node_paths np
    JOIN parent p ON np.child_id = p.parent_id
)
-- 去重并按预期格式排序输出
SELECT DISTINCT parent_id, child_id, dist
FROM node_paths
ORDER BY parent_id, dist, child_id
-- OPTION (MAXRECURSION 0) -- 当节点层级超过100时取消注释启用

修正说明 #

• 初始CTE直接生成每个节点的自身路径，保证所有节点都能作为递归起点。
• 递归部分通过np.child_id = p.parent_id正确关联父节点和子节点，实现层级遍历并累加距离。
• 最终输出字段完全匹配预期格式，排序后结果更易读。

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