获取父子关系分类表中的全部分类层级

表结构与示例数据 #

category表采用父子关系存储分类信息，结构及示例数据如下：

需求说明 #

需要提取每条分类的完整层级路径，实现类似多级分类导航的展示效果（例如从顶级分类Car到末级2020的完整路径：Car > Porsche > 911 > Petrol, SUV > > 3.6L > > 3606CC H6 GAS SOHC Naturally Aspirated > > 2020）。

解决方案 #

1. 递归CTE实现（推荐，适用于MySQL 8.0+、PostgreSQL、SQL Server等） #

递归公共表表达式（CTE）可以高效遍历父子层级关系，自动拼接完整路径：

WITH RECURSIVE category_hierarchy AS (
    -- 初始查询：获取所有顶级分类（父ID为空）
    SELECT 
        cat_id,
        cat_parent_id,
        cat_name,
        CAST(cat_name AS VARCHAR(1000)) AS full_path
    FROM category
    WHERE cat_parent_id IS NULL OR cat_parent_id = ''

    UNION ALL

    -- 递归关联子分类，拼接路径
    SELECT 
        c.cat_id,
        c.cat_parent_id,
        c.cat_name,
        CONCAT(ch.full_path, ' > ', c.cat_name) AS full_path
    FROM category c
    INNER JOIN category_hierarchy ch 
        ON c.cat_parent_id = ch.cat_id
)
SELECT cat_id, cat_name, full_path
FROM category_hierarchy
ORDER BY cat_id;

2. 非递归自连接实现（适用于不支持递归的旧版数据库） #

如果数据库不支持递归CTE，可通过多次自连接拼接路径（仅适用于固定层级的分类，本例为7级）：

-- 提取末级分类路径
SELECT 
    c7.cat_id,
    c7.cat_name,
    CONCAT(c1.cat_name, ' > ', c2.cat_name, ' > ', c3.cat_name, ' > ', c4.cat_name, ' > ', c5.cat_name, ' > ', c6.cat_name, ' > ', c7.cat_name) AS full_path
FROM category c1
LEFT JOIN category c2 ON c2.cat_parent_id = c1.cat_id
LEFT JOIN category c3 ON c3.cat_parent_id = c2.cat_id
LEFT JOIN category c4 ON c4.cat_parent_id = c3.cat_id
LEFT JOIN category c5 ON c5.cat_parent_id = c4.cat_id
LEFT JOIN category c6 ON c6.cat_parent_id = c5.cat_id
LEFT JOIN category c7 ON c7.cat_parent_id = c6.cat_id
WHERE c7.cat_id IS NOT NULL

UNION ALL

-- 提取倒数第二级分类路径
SELECT 
    c6.cat_id,
    c6.cat_name,
    CONCAT(c1.cat_name, ' > ', c2.cat_name, ' > ', c3.cat_name, ' > ', c4.cat_name, ' > ', c5.cat_name, ' > ', c6.cat_name) AS full_path
FROM category c1
LEFT JOIN category c2 ON c2.cat_parent_id = c1.cat_id
LEFT JOIN category c3 ON c3.cat_parent_id = c2.cat_id
LEFT JOIN category c4 ON c4.cat_parent_id = c3.cat_id
LEFT JOIN category c5 ON c5.cat_parent_id = c4.cat_id
LEFT JOIN category c6 ON c6.cat_parent_id = c5.cat_id
WHERE c6.cat_id IS NOT NULL 
  AND NOT EXISTS (SELECT 1 FROM category WHERE cat_parent_id = c6.cat_id)

-- 可继续补充上层分类的路径查询，根据实际层级调整

执行结果示例 #

运行递归CTE查询后，会得到包含完整层级路径的结果：

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