在SQL Server中用HierarchyID和递归CTE显示完整产品分类路径

你已经通过传统ParentID外键实现了分类路径需求，现在要基于HierarchyID数据类型生成每个产品主分类的完整层级路径，以下是具体实现方案：

你的表结构 #

CREATE TABLE tbl2ProductCategories (
    -- Primary Key Field
    ProductCategoryID INT NOT NULL IDENTITY(1,1),
    -- Non Key Fields
    Node HIERARCHYID NOT NULL UNIQUE,
    ProductCategory NVARCHAR(50) NOT NULL,
    -- Constraints
    CONSTRAINT PK_tbl2ProductCategories PRIMARY KEY (ProductCategoryID)
);

CREATE TABLE tbl1Products (
    -- Primary Key Field
    ProductID INT NOT NULL IDENTITY(1,1),
    -- Non Key Fields
    ProductName NVARCHAR(50) NOT NULL UNIQUE,
    -- Constraints
    CONSTRAINT PK_tbl1Products PRIMARY KEY (ProductID),
);

-- Each product can be in multiple categories
CREATE TABLE tbl3ProductsCategories (
    -- Primary Key Field
    ProductID INT NOT NULL,
    ProductCategoryID INT NOT NULL,
    -- Non Key Fields
    IsPrimaryCategory BIT NOT NULL DEFAULT 1
    -- Constraints
    CONSTRAINT PK_tbl3ProductsCategories PRIMARY KEY (ProductID, ProductCategoryID),
    CONSTRAINT FK_tbl3ProductsCategories_tbl1Products FOREIGN KEY (ProductID) REFERENCES tbl1Products (ProductID),
    CONSTRAINT FK_tbl3ProductsCategories_tbl2ProductCategories FOREIGN KEY (ProductCategoryID) REFERENCES tbl2ProductCategories (ProductCategoryID)
);

实现步骤 #

1. 确保分类表的HierarchyID层级正确 #

首先需要保证tbl2ProductCategories中的Node字段正确维护了分类的层级关系，比如根节点为/，子节点为/1/、/1/1/等。可以插入测试数据验证：

-- 插入测试分类数据
INSERT INTO tbl2ProductCategories (Node, ProductCategory)
VALUES
    (HierarchyID::GetRoot(), 'Products'), -- 根节点 /
    (HierarchyID::GetRoot().GetDescendant(NULL, NULL), 'Category1'), -- /1/
    (HierarchyID::Parse('/1/').GetDescendant(NULL, NULL), 'Subcategory1'), -- /1/1/
    (HierarchyID::Parse('/1/1/').GetDescendant(NULL, NULL), 'Subsubcategory1'), -- /1/1/1/
    (HierarchyID::Parse('/1/').GetDescendant(HierarchyID::Parse('/1/1/'), NULL), 'Subcategory3'), -- /1/2/
    (HierarchyID::GetRoot().GetDescendant(HierarchyID::Parse('/1/'), NULL), 'Category5'); -- /2/

-- 插入测试产品
INSERT INTO tbl1Products (ProductName)
VALUES ('Product 1'), ('Product 2'), ('Product 3');

-- 关联主分类
INSERT INTO tbl3ProductsCategories (ProductID, ProductCategoryID, IsPrimaryCategory)
VALUES
    (1, (SELECT ProductCategoryID FROM tbl2ProductCategories WHERE ProductCategory = 'Subsubcategory1'), 1),
    (2, (SELECT ProductCategoryID FROM tbl2ProductCategories WHERE ProductCategory = 'Subcategory3'), 1),
    (3, (SELECT ProductCategoryID FROM tbl2ProductCategories WHERE ProductCategory = 'Category5'), 1);

2. 编写查询生成完整路径 #

通过递归CTE遍历HierarchyID的层级关系，拼接每个分类的完整路径，再关联产品和主分类数据：

-- 递归CTE获取每个分类的完整路径
WITH CategoryPaths AS (
    -- 锚点成员：根节点
    SELECT
        ProductCategoryID,
        Node,
        ProductCategory,
        CAST(ProductCategory AS NVARCHAR(MAX)) AS FullCategoryPath
    FROM tbl2ProductCategories
    WHERE Node = HierarchyID::GetRoot()

    UNION ALL

    -- 递归成员：子节点拼接父节点路径
    SELECT
        c.ProductCategoryID,
        c.Node,
        c.ProductCategory,
        CAST(cp.FullCategoryPath + ' > ' + c.ProductCategory AS NVARCHAR(MAX)) AS FullCategoryPath
    FROM tbl2ProductCategories c
    JOIN CategoryPaths cp ON c.Node.GetAncestor(1) = cp.Node
)
-- 关联产品和主分类，获取最终结果
SELECT
    p.ProductName + ': ' + cp.FullCategoryPath AS ProductWithCategoryPath
FROM tbl1Products p
JOIN tbl3ProductsCategories pc ON p.ProductID = pc.ProductID
JOIN CategoryPaths cp ON pc.ProductCategoryID = cp.ProductCategoryID
WHERE pc.IsPrimaryCategory = 1
ORDER BY p.ProductID;

结果说明 #

执行上述查询后，会输出符合需求的结果：

• Product 1: Products > Category1 > Subcategory1 > Subsubcategory1
• Product 2: Products > Category1 > Subcategory3
• Product 3: Products > Category5

对你原有CTE的修正说明 #

你之前的递归CTE没有关联分类表，也未利用HierarchyID的GetAncestor()方法建立层级关联，导致无法拼接路径。上述方案通过锚点成员定位根分类，递归成员逐层关联父节点并拼接名称，最终结合产品关联数据得到目标结果。

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