PostgreSQL优雅的进行递归查询的实战指南_MsSqlserver

在实际开发中，我们经常会遇到树形结构或图结构的数据需求，比如：

组织架构（部门 → 子部门）
商品分类（一级类目 → 二级类目 → …）
评论回复（评论 → 回复 → 回复的回复）
权限继承（角色 → 子角色）
路径查找（最短路径、依赖关系）

这些场景的核心问题是：如何高效查询具有层级/递归关系的数据？

postgresql 提供了强大的 with recursive（公共表表达式递归） 功能，是处理此类问题的标准 sql 解决方案。本文将从基础到实战，手把手教你掌握递归查询的精髓。

一、递归查询基础：cte 与with recursive

1.1 什么是 cte（common table expression）？

cte 是一种临时结果集，可被主查询引用，语法如下：

with cte_name as (
    -- 查询语句
)
select * from cte_name;

优点：提升 sql 可读性、避免重复子查询、支持递归

1.2 递归 cte 的基本结构

with recursive cte_name as (
    -- 1. 初始查询（锚点成员 anchor member）
    select ... from table where ...

    union [all]

    -- 2. 递归查询（递归成员 recursive member）
    select ... from table, cte_name where ...
)
select * from cte_name;

核心三要素：

部分	作用	注意事项
初始查询	定义递归起点（如根节点）	必须能终止递归
union [all]	合并结果集	`union` 去重，`union all` 保留重复（性能更高）
递归查询	引用自身 cte，向下/向上遍历	必须有连接条件，避免无限循环

1.3 递归查询的建议

场景	推荐方案
标准树形查询（上下级）	`with recursive` + `union all`
防循环	记录访问路径 `array[id]` + `!= all(path)`
限制深度	添加 `depth` 字段 + `where depth < n`
高性能读	物化路径 / 闭包表（写少读多）
返回树形 json	自底向上聚合 + `jsonb_build_object`
python 集成	直接执行原生 sql（sqlalchemy 支持 cte）

终极建议：
“90% 的树形查询，一个精心设计的 with recursive 就够了。”
只有在性能成为瓶颈时，才考虑物化路径等复杂模型。

二、经典场景实战：组织架构查询

假设有一张部门表 departments：

create table departments (
    id serial primary key,
    name varchar(100) not null,
    parent_id integer references departments(id)
);

-- 插入示例数据
insert into departments (name, parent_id) values
('总公司', null),
('技术部', 1),
('产品部', 1),
('前端组', 2),
('后端组', 2),
('ios组', 2),
('设计组', 3);

2.1 查询“技术部”及其所有子部门（向下递归）

with recursive dept_tree as (
    -- 锚点：找到“技术部”
    select id, name, parent_id, 0 as level
    from departments
    where name = '技术部'

    union all

    -- 递归：找子部门
    select d.id, d.name, d.parent_id, dt.level + 1
    from departments d
    inner join dept_tree dt on d.parent_id = dt.id
)
select 
    lpad('', level * 4, ' ') || name as hierarchy,  -- 缩进显示层级
    id, parent_id, level
from dept_tree
order by level;

输出结果：

hierarchy        | id | parent_id | level
-----------------|----|-----------|------
技术部           | 2  | 1         | 0
    前端组       | 4  | 2         | 1
    后端组       | 5  | 2         | 1
    ios组        | 6  | 2         | 1

技巧：lpad('', level * 4, ' ') 生成缩进，直观展示树形结构

2.2 查询“后端组”的完整上级路径（向上递归）

with recursive dept_path as (
    -- 锚点：从“后端组”开始
    select id, name, parent_id, 0 as level
    from departments
    where name = '后端组'

    union all

    -- 递归：找父部门
    select d.id, d.name, d.parent_id, dp.level + 1
    from departments d
    inner join dept_path dp on d.id = dp.parent_id
    where dp.parent_id is not null  -- 避免 null 连接
)
select 
    repeat(' → ', level) || name as path_from_root
from dept_path
order by level desc;  -- 从根到当前节点

输出结果：

path_from_root
---------------------------
总公司 → 技术部 → 后端组

三、高级技巧：控制递归深度与防环

3.1 限制递归深度（防止无限循环）

with recursive dept_limited as (
    select id, name, parent_id, 1 as depth
    from departments
    where parent_id is null  -- 从根开始

    union all

    select d.id, d.name, d.parent_id, dl.depth + 1
    from departments d
    inner join dept_limited dl on d.parent_id = dl.id
    where dl.depth < 3  -- 最多查3层
)
select * from dept_limited;

3.2 检测并避免循环引用（图结构必备）

如果数据存在循环（如 a→b→c→a），递归会无限进行。解决方案：记录访问路径。

with recursive graph_traversal as (
    -- 锚点
    select 
        id, 
        name, 
        parent_id,
        array[id] as path,      -- 记录已访问节点
        1 as depth
    from departments
    where name = '技术部'

    union all

    -- 递归
    select 
        d.id,
        d.name,
        d.parent_id,
        gt.path || d.id,        -- 追加当前节点
        gt.depth + 1
    from departments d
    inner join graph_traversal gt on d.parent_id = gt.id
    where 
        d.id != all(gt.path)    -- 关键：当前节点不在已访问路径中
        and gt.depth < 10       -- 安全兜底
)
select * from graph_traversal;

d.id != all(gt.path) 确保不重复访问节点，彻底解决循环问题

3.3 反向应用：扁平数据转树形 json

postgresql 支持将递归结果直接转为 嵌套 json，适合 api 返回。如使用 jsonb_build_object 构建树

with recursive tree as (
    -- 叶子节点（无子节点）
    select 
        id,
        name,
        parent_id,
        jsonb_build_object('id', id, 'name', name, 'children', '[]'::jsonb) as node
    from categories c1
    where not exists (
        select 1 from categories c2 where c2.parent_id = c1.id
    )

    union all

    -- 非叶子节点（聚合子节点）
    select 
        p.id,
        p.name,
        p.parent_id,
        jsonb_build_object(
            'id', p.id,
            'name', p.name,
            'children', jsonb_agg(t.node)
        ) as node
    from categories p
    inner join tree t on t.parent_id = p.id
    group by p.id, p.name, p.parent_id
)
select node 
from tree 
where parent_id is null;  -- 返回根节点

输出 json：

{
  "id": 1,
  "name": "电子产品",
  "children": [
    {
      "id": 2,
      "name": "手机",
      "children": [
        {"id": 3, "name": "iphone", "children": []},
        {"id": 4, "name": "华为", "children": []}
      ]
    },
    {
      "id": 5,
      "name": "电脑",
      "children": [
        {"id": 6, "name": "笔记本", "children": []}
      ]
    }
  ]
}

此方法利用 自底向上聚合，天然避免循环，但要求数据为严格树形（无环）

四、实战案例：商品分类树

4.1 场景：电商商品分类（多级类目）

create table categories (
    id serial primary key,
    name varchar(100) not null,
    parent_id integer references categories(id),
    is_leaf boolean default false  -- 是否叶子节点
);

-- 插入数据
insert into categories (name, parent_id, is_leaf) values
('电子产品', null, false),
('手机', 1, false),
('iphone', 2, true),
('华为', 2, true),
('电脑', 1, false),
('笔记本', 5, true);

4.2 查询“电子产品”下所有叶子类目（带完整路径）

with recursive category_tree as (
    -- 锚点：根类目
    select 
        id, 
        name, 
        parent_id,
        name::text as full_path,  -- 路径字符串
        1 as level
    from categories
    where name = '电子产品'

    union all

    -- 递归：拼接路径
    select 
        c.id,
        c.name,
        c.parent_id,
        ct.full_path || ' > ' || c.name,  -- 路径拼接
        ct.level + 1
    from categories c
    inner join category_tree ct on c.parent_id = ct.id
)
select 
    full_path,
    id,
    level
from category_tree
where is_leaf = true;  -- 只查叶子节点

输出：

full_path                   | id | level
----------------------------|----|------
电子产品 > 手机 > iphone   | 3  | 3
电子产品 > 手机 > 华为     | 4  | 3
电子产品 > 电脑 > 笔记本   | 6  | 3

4.3 python + sqlalchemy 实战

在 python 中使用递归查询：

from sqlalchemy import text
from sqlalchemy.orm import sessionmaker

def get_dept_tree(session, root_name):
    query = text("""
        with recursive dept_tree as (
            select id, name, parent_id, 0 as level
            from departments
            where name = :root_name
            union all
            select d.id, d.name, d.parent_id, dt.level + 1
            from departments d
            inner join dept_tree dt on d.parent_id = dt.id
        )
        select * from dept_tree order by level;
    """)
    
    result = session.execute(query, {"root_name": root_name})
    return result.fetchall()

# 使用
with session() as session:
    tree = get_dept_tree(session, "技术部")
    for row in tree:
        print(f"{'  ' * row.level}{row.name}")

五、性能优化：索引与执行计划

5.1 必建索引

-- 对 parent_id 建索引（递归连接的关键）
create index idx_departments_parent_id on departments(parent_id);

-- 如果常按 name 查询根节点
create index idx_departments_name on departments(name);

5.2 查看执行计划

explain (analyze, buffers)
with recursive ... ;  -- 你的递归查询

关键观察点：

是否使用了 index scan（而非 seq scan）
递归深度是否合理
内存使用（buffers）

5.3 大数据量优化建议

问题	解决方案
递归太深（>100层）	限制 `depth < n`，业务上通常不需要过深层级
数据量大（百万级）	分页查询（先查id再关联）、物化路径（见下文）
频繁查询	使用物化路径（materialized path）或闭包表（closure table）

六、替代方案对比：何时不用递归？

虽然 with recursive 很强大，但在某些场景下，其他模型更高效：

6.1 物化路径（materialized path）

在每条记录中存储完整路径：

alter table categories add column path text; -- 如 "/1/2/3/"

-- 查询“手机”下所有子类目
select * from categories 
where path like '/1/2/%';

✅ 优点：查询极快（走索引）
❌ 缺点：移动节点时需更新大量 path

6.2 闭包表（closure table）

额外建一张表存储所有祖先-后代关系：

create table category_closure (
    ancestor_id int,
    descendant_id int,
    depth int
);

-- 查询“手机”(id=2)的所有后代
select c.* 
from categories c
join category_closure cl on c.id = cl.descendant_id
where cl.ancestor_id = 2;

✅ 优点：查询快，支持任意深度
❌ 缺点：写操作复杂，存储空间大

选择建议：

读多写少 + 深度固定 → 物化路径
频繁查询全路径 → 闭包表
通用场景 + 中小数据量 → with recursive

七、常见陷阱与避坑指南

陷阱 1：忘记where条件导致无限循环

-- 错误：缺少终止条件
select ... from table, cte where table.parent_id = cte.id
-- 如果存在循环引用，永远停不下来！

✅ 解决：始终加上 depth < n 或路径检测

陷阱 2：使用union而非union all

union 会去重，但递归中通常不需要（父子id唯一）
性能损失高达 30%+

✅ 解决：除非明确需要去重，否则用 union all

陷阱 3：在递归部分使用聚合函数

-- 错误：递归成员不能包含聚合
select ..., count(*) from ... join cte ...

✅ 解决：先递归，再在外层聚合

以上就是postgresql优雅的进行递归查询的实战指南的详细内容，更多关于postgresql进行递归查询的资料请关注代码网其它相关文章！

PostgreSQL优雅的进行递归查询的实战指南