如何使用递归CTE查询SQL Server中特定子节点的完整层级深度？

问题分析 #

你当前使用的递归CTE是自上而下的遍历逻辑：从根节点（p_key = 'old'的行）开始，逐层向下查询所有子节点，所以默认会返回全量链路数据。你之前在UNION ALL的递归查询段加WHERE筛选失败，是因为递归过程中直接过滤会打断链路遍历，最终只能返回锚点段的深度0数据。

解决方案 #

方案1：仅需获取child节点的深度值 #

不需要修改递归逻辑，直接在CTE的最终查询语句加过滤条件即可：

WITH cte_data (c_key, p_key, depth) AS
(
    SELECT c_key, p_key, 0 AS depth
    FROM my_table
    WHERE p_key = 'old'

    UNION ALL

    SELECT c.c_key, c.p_key, o.depth + 1 AS depth
    FROM cte_data o
    INNER JOIN my_table c ON o.c_key = c.p_key
)
SELECT * 
FROM cte_data
WHERE c_key = 'child'

执行后会返回c_key = 'child'的单行结果，对应深度为2。

方案2：需要获取child到根节点的完整层级链路 #

推荐将递归逻辑改为自下而上遍历，直接从目标子节点出发向上找父节点，只会遍历你需要的链路，数据量较大时性能远优于全量遍历后过滤：

WITH cte_data (c_key, p_key, depth) AS
(
    -- 锚点：从目标子节点开始查询
    SELECT c_key, p_key, 0 AS depth
    FROM my_table
    WHERE c_key = 'child'

    UNION ALL

    -- 递归向上关联父节点
    SELECT o.c_key, o.p_key, c.depth + 1 AS depth
    FROM my_table o
    INNER JOIN cte_data c ON o.c_key = c.p_key
)
SELECT * 
FROM cte_data

执行后会返回完整链路：child（深度0）→teen（深度1）→adult（深度2），如果需要把根节点old也纳入结果，补充一行合并逻辑即可：

WITH cte_data (c_key, p_key, depth) AS
(
    SELECT c_key, p_key, 0 AS depth
    FROM my_table
    WHERE c_key = 'child'

    UNION ALL

    SELECT o.c_key, o.p_key, c.depth + 1 AS depth
    FROM my_table o
    INNER JOIN cte_data c ON o.c_key = c.p_key
)
SELECT c_key, p_key, depth FROM cte_data
UNION ALL
SELECT 'old' AS c_key, NULL AS p_key, MAX(depth)+1 AS depth FROM cte_data

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