如何使用SQL Server的HIERARCHYID生成产品分类面包屑导航？

不需要像传统ParentID那样写复杂的递归CTE，HIERARCHYID自带的层级操作方法可以更高效地生成面包屑，下面提供两种可行方案：

方案1：递归CTE结合HIERARCHYID #

这种方式逻辑直观，适配所有SQL Server版本：

WITH CategoryHierarchy AS (
    -- 锚点成员：根节点
    SELECT 
        CategoryID,
        Node,
        CategoryName,
        CAST(CategoryName AS NVARCHAR(MAX)) AS Breadcrumb,
        Node.Level AS HierarchyLevel
    FROM [Product Categories]
    WHERE Node = 0x -- 定位根节点
    
    UNION ALL
    
    -- 递归成员：关联子节点与父节点
    SELECT 
        c.CategoryID,
        c.Node,
        c.CategoryName,
        CAST(ch.Breadcrumb + ' > ' + c.CategoryName AS NVARCHAR(MAX)) AS Breadcrumb,
        c.Node.Level AS HierarchyLevel
    FROM [Product Categories] c
    INNER JOIN CategoryHierarchy ch ON c.Node.GetAncestor(1) = ch.Node
)
SELECT 
    CategoryID,
    CategoryName,
    Breadcrumb
FROM CategoryHierarchy
ORDER BY HierarchyLevel, CategoryID;

方案2：利用STRING_AGG与HIERARCHYID（SQL Server 2017+） #

如果你的SQL Server版本支持STRING_AGG，可以用更简洁的写法，无需递归CTE：

SELECT 
    c.CategoryID,
    c.CategoryName,
    STRING_AGG(ancestor.CategoryName, ' > ') WITHIN GROUP (ORDER BY level.i) AS Breadcrumb
FROM [Product Categories] c
-- 生成当前节点的所有层级序列（从0到节点自身的Level值）
CROSS APPLY (
    SELECT TOP (c.Node.Level + 1) 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS i
    FROM sys.all_columns
) level
-- 关联每个层级对应的祖先节点
JOIN [Product Categories] ancestor ON c.Node.GetAncestor(level.i) = ancestor.Node
GROUP BY c.CategoryID, c.CategoryName
ORDER BY c.CategoryID;

结果示例 #

两种方案都会输出符合需求的结果：

核心HIERARCHYID方法说明 #

• Node.GetAncestor(n)：获取当前节点向上n级的祖先节点，GetAncestor(1)对应直接父节点
• Node.Level：返回当前节点的层级（根节点Level为0，一级子节点为1，以此类推）
• STRING_AGG：SQL Server 2017引入的聚合函数，用于按指定分隔符拼接多行字符串

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