用递归CTE玩转层级结构的例子

想象一下：你有个网店，上千种商品，分门别类地放好——有分类，有子分类，还有子子分类。网站上看着是个漂亮的下拉菜单，但在数据库里就头大了。怎么用一个查询把“电子产品 → 智能手机 → 配件”这整条链都查出来？怎么统计每个分类有多少层嵌套？普通的JOIN根本搞不定——得用递归才行！

用递归CTE构建商品分类结构

在关系型数据库里，处理层级结构是个经典难题。比如你有个商品分类树：主分类、子分类、子子分类，等等。比如：

电子产品
  └── 智能手机
      └── 配件
  └── 笔记本
      └── 游戏本
  └── 摄影和视频

这种结构在网店界面里很好展示，但怎么在数据库里存和查？这时候递归CTE就派上用场了！

分类表的原始结构

先来建个categories表，用来存商品分类的数据：

CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,       -- 分类唯一ID
    category_name TEXT NOT NULL,          -- 分类名
    parent_category_id INT                -- 父分类（主分类就是NULL）
);

比如我们往表里加点数据：

INSERT INTO categories (category_name, parent_category_id) VALUES
    ('电子产品', NULL),
    ('智能手机', 1),
    ('配件', 2),
    ('笔记本', 1),
    ('游戏本', 4),
    ('摄影和视频', 1);

这里发生了啥：

• 电子产品 —— 这是主分类（没有父类，parent_category_id = NULL）。
• 智能手机属于电子产品。
• 配件属于智能手机。
• 其他分类也是类似的关系。

现在categories表里的数据结构大概是这样：

用递归CTE构建分类树

现在我们想查出所有分类的层级结构，还要知道每个的嵌套层数。用递归CTE就能搞定。

WITH RECURSIVE category_tree AS (
    -- 基础查询：选出所有根分类（parent_category_id = NULL）
    SELECT
        category_id,
        category_name,
        parent_category_id,
        1 AS depth -- 第一层
    FROM categories
    WHERE parent_category_id IS NULL

    UNION ALL

    -- 递归查询：找每个分类的子分类
    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.depth + 1 AS depth -- 层级加一
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)
-- 最终查询：从CTE里取结果
SELECT
    category_id,
    category_name,
    parent_category_id,
    depth
FROM category_tree
ORDER BY depth, parent_category_id, category_id;

结果：

这里发生了啥？

1. 先用基础查询（SELECT … FROM categories WHERE parent_category_id IS NULL）选出主分类。这里只有电子产品，depth = 1。
2. 然后递归查询用INNER JOIN把子分类加进来，层级depth + 1。
3. 这个过程会一直递归下去，直到所有层级的子分类都找完。

实用小升级

基础例子能用，但实际项目里经常要更多功能。比如你要做面包屑导航，或者想让经理看看哪个分类下子分类最多。来看看怎么让查询更实用。

1. 加上完整分类路径

有时候显示完整路径很有用，比如：电子产品 > 智能手机 > 配件。可以用字符串拼接来实现：

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        category_name,
        parent_category_id,
        category_name AS full_path,
        1 AS depth
    FROM categories
    WHERE parent_category_id IS NULL

    UNION ALL

    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.full_path || ' > ' || c.category_name AS full_path, -- 拼接路径
        ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)

SELECT
    category_id,
    category_name,
    parent_category_id,
    full_path,
    depth
FROM category_tree
ORDER BY depth, parent_category_id, category_id;

结果：

现在每个分类都有完整路径，嵌套关系一目了然。

2. 统计子分类数量

如果想知道每个分类下有多少子分类怎么办？

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        parent_category_id
    FROM categories

    UNION ALL

    SELECT
        c.category_id,
        c.parent_category_id
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)

SELECT
    parent_category_id,
    COUNT(*) AS subcategory_count
FROM category_tree
WHERE parent_category_id IS NOT NULL
GROUP BY parent_category_id
ORDER BY parent_category_id;

结果：

表里显示，电子产品有3个子分类（智能手机、笔记本、摄影和视频），智能手机和笔记本各有一个。

用递归CTE时的注意点和常见坑

死循环：如果数据里有环（比如某分类指向自己），查询会死循环。可以用WHERE depth < N或者加限制来防止。

性能优化：递归CTE在大数据量下会慢，记得给parent_category_id加索引提速。

用UNION而不是UNION ALL的坑：递归CTE一定要用UNION ALL，不然PostgreSQL会去重，查询会变慢。

这个例子展示了递归CTE怎么帮你搞定层级结构。学会从数据库里查出层级关系，在很多实际项目里都超有用，比如做网站菜单、分析组织结构、或者玩图结构。现在你已经可以应对各种复杂需求啦！

1

调查/小测验

CTE入门第 27 级，课程 4

不可用

CTE入门

CTE入门

打开