实现按层级列展示产品所属分类的查询需求

我有一组产品数据，每个产品对应一个默认分类（CategoryDefaultId），分类采用层级结构组织。分类数据如下：

分类表 #

需要为每个产品展示所属的各级分类，从最高层级到最低层级依次排列，预期结果如下：

预期结果表 #

尝试多种方法未得到预期结果，求解决方案。

可以使用**递归CTE（公共表表达式）**遍历分类层级，提取每个分类的完整路径后与产品表关联，再拆分路径到对应列中，具体实现如下：

1. 递归生成分类完整路径 #

通过递归查询，为每个分类生成从根节点到自身的层级路径，并记录层级数：

WITH CategoryHierarchy AS (
    -- 锚点成员：根分类（父ID为NULL）
    SELECT 
        CategoryId,
        CategoryParentId,
        Designation,
        CAST(Designation AS VARCHAR(MAX)) AS CategoryPath,
        1 AS Level
    FROM Categories
    WHERE CategoryParentId IS NULL

    UNION ALL

    -- 递归成员：遍历子分类，拼接路径
    SELECT 
        c.CategoryId,
        c.CategoryParentId,
        c.Designation,
        CONCAT(ch.CategoryPath, '|', c.Designation) AS CategoryPath,
        ch.Level + 1 AS Level
    FROM Categories c
    INNER JOIN CategoryHierarchy ch ON c.CategoryParentId = ch.CategoryId
)

2. 关联产品表并拆分路径 #

将产品表与递归结果关联，通过字符串拆分函数把路径拆分为对应层级的分类列：

SELECT 
    p.ProductId,
    p.Designation,
    p.CategoryDefaultId,
    -- 拆分路径到对应列，适配最多4级分类
    PARSENAME(REPLACE(ch.CategoryPath, '|', '.'), 4) AS [Category],
    PARSENAME(REPLACE(ch.CategoryPath, '|', '.'), 3) AS [Category 2],
    PARSENAME(REPLACE(ch.CategoryPath, '|', '.'), 2) AS [Category 3],
    PARSENAME(REPLACE(ch.CategoryPath, '|', '.'), 1) AS [Category 4]
FROM Products p
INNER JOIN CategoryHierarchy ch ON p.CategoryDefaultId = ch.CategoryId
ORDER BY p.ProductId;

补充说明 #

• 使用|作为路径分隔符，替换为.是因为PARSENAME默认按.拆分，且从右往左计数，刚好匹配从根到子的层级顺序。
• 若数据库不支持PARSENAME，可替换为对应数据库的字符串拆分函数：比如MySQL用SUBSTRING_INDEX，PostgreSQL用STRING_TO_ARRAY。
• 若分类层级超过4级，只需调整PARSENAME参数或新增对应列即可。

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