如何在SQL Server（T-SQL、SQL）中实现指定规则的层级查询？

针对你提出的层级结构查询需求，我整理了一个基于递归CTE（公共表表达式）的实现方案，完全适配你给出的数据规则和期望输出。

先明确表结构 #

首先假设你的数据表名为HierarchyTable，数据行如下（还原你提供的原始数据）：

规则回顾：当ref_id与parent_id相等时，该节点为树的顶层（根节点），我们需要输出从最底层子节点到顶层节点的完整层级链。

T-SQL实现代码 #

WITH RecursiveHierarchy AS (
    -- 锚点成员：筛选所有非顶层节点，初始化路径为当前节点ID
    SELECT 
        ref_id,
        parent_id,
        CAST(ref_id AS VARCHAR(MAX)) AS HierarchyPath
    FROM 
        HierarchyTable
    WHERE 
        ref_id != parent_id

    UNION ALL

    -- 递归成员：向上追溯父节点，拼接路径
    SELECT 
        rh.ref_id,
        ht.parent_id,
        CAST(rh.HierarchyPath + ' ' + ht.ref_id AS VARCHAR(MAX)) AS HierarchyPath
    FROM 
        RecursiveHierarchy rh
    INNER JOIN 
        HierarchyTable ht ON rh.parent_id = ht.ref_id
    WHERE 
        ht.ref_id != ht.parent_id -- 未到达顶层节点时继续递归
)
-- 最终拼接顶层节点，输出完整层级链
SELECT 
    rh.HierarchyPath + ' ' + ht.ref_id AS FullHierarchy
FROM 
    RecursiveHierarchy rh
INNER JOIN 
    HierarchyTable ht ON rh.parent_id = ht.ref_id
WHERE 
    ht.ref_id = ht.parent_id -- 仅保留到达顶层节点的完整路径
ORDER BY 
    FullHierarchy;

代码说明 #

• 锚点成员：先筛选出所有非顶层的节点，把当前节点的ref_id作为路径的起始部分。
• 递归成员：通过关联父节点，不断将父节点ID拼接到路径末尾，直到父节点不是顶层节点为止。
• 最终查询：把递归到的父节点（顶层节点）拼接到路径最后，得到完整的层级链，再筛选出所有到达顶层的路径并排序。

执行结果 #

运行上述代码后，会输出与你期望完全一致的结果：

A B C D
P Q R
X Y

额外说明 #

• 如果你的ref_id/parent_id是数值类型（如INT），只需调整CAST的目标类型（比如CAST(ref_id AS NVARCHAR(MAX))）即可。
• 如果存在循环引用（除了顶层节点的自引用），可以添加OPTION(MAXRECURSION 0)来允许无限递归（谨慎使用，避免死循环），或者在递归中添加路径重复判断。

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