CTE 与递归查询

WITH（CTE，Common Table Expression）可以让你在 SQL 中像写程序一样组织逻辑——分步骤、命名中间结果、复用。递归 CTE 更是处理层级数据的利器。

普通 CTE

CTE 把复杂的查询拆成多个可读的步骤：

-- 统计每个城市的客户消费情况
WITH city_stats AS (
    SELECT 
        c.city,
        COUNT(DISTINCT o.customer_id) AS 客户数,
        SUM(o.total_amount) AS 总消费
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
        AND o.status = '已完成'
    GROUP BY c.city
),
avg_city AS (
    SELECT AVG(总消费) AS 城市平均消费 FROM city_stats
)
SELECT 
    cs.city,
    cs.客户数,
    cs.总消费,
    ROUND(cs.总消费 / ac.城市平均消费 * 100, 2) AS 相对占比
FROM city_stats cs
CROSS JOIN avg_city ac
ORDER BY cs.总消费 DESC;

CTE 的优势

可读性：分步骤命名，逻辑清晰
可复用：一个 CTE 可以在同一查询中引用多次
调试友好：可以单独运行 CTE 部分验证

-- 单独运行 CTE 部分来调试
WITH city_stats AS (
    SELECT city, COUNT(DISTINCT customer_id) AS 客户数
    FROM customers
    GROUP BY city
)
SELECT * FROM city_stats;

递归 CTE

递归 CTE 用于处理树状或图状结构——如组织结构、分类层级、菜单树等。

语法结构

WITH RECURSIVE 名称 AS (
    -- 非递归部分：锚点（起点）
    SELECT ...
    UNION ALL
    -- 递归部分：引用自身
    SELECT ... FROM 名称 WHERE ...
)
SELECT * FROM 名称;

示例：组织架构树

-- 创建组织架构表
CREATE TABLE employees_hierarchy (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    manager_id INTEGER REFERENCES employees_hierarchy(id),
    department VARCHAR(50),
    salary NUMERIC(10,2)
);

INSERT INTO employees_hierarchy (name, manager_id, department, salary) VALUES
    ('张大鹏', NULL,   '总公司', 50000),  -- id=1，CEO
    ('李明',    1,      '技术部', 30000),  -- id=2
    ('王芳',    1,      '市场部', 28000),  -- id=3
    ('赵强',    2,      '技术部', 22000),  -- id=4
    ('孙丽',    2,      '技术部', 20000),  -- id=5
    ('钱华',    3,      '市场部', 21000),  -- id=6
    ('周敏',    3,      '市场部', 18000),  -- id=7
    ('吴刚',    4,      '技术部', 15000),  -- id=8
    ('郑秀',    5,      '技术部', 13000),  -- id=9
    ('陈杰',    7,      '市场部', 12000);  -- id=10

-- 递归查询：找出所有下属层级
WITH RECURSIVE org_tree AS (
    -- 非递归：从 CEO 开始
    SELECT 
        id,
        name,
        manager_id,
        department,
        1 AS 层级,
        name::TEXT AS 路径
    FROM employees_hierarchy
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归部分：找下属
    SELECT 
        e.id,
        e.name,
        e.manager_id,
        e.department,
        ot.层级 + 1,
        ot.路径 || ' → ' || e.name
    FROM employees_hierarchy e
    INNER JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree
ORDER BY 路径;

输出：

 id |  name  | manager_id | department | 层级 |                    路径                     
----+--------+------------+------------+------+---------------------------------------------
  1 | 张大鹏 |            | 总公司     |    1 | 张大鹏
  2 | 李明   |          1 | 技术部     |    2 | 张大鹏 → 李明
  4 | 赵强   |          2 | 技术部     |    3 | 张大鹏 → 李明 → 赵强
  8 | 吴刚   |          4 | 技术部     |    4 | 张大鹏 → 李明 → 赵强 → 吴刚
  5 | 孙丽   |          2 | 技术部     |    3 | 张大鹏 → 李明 → 孙丽
  9 | 郑秀   |          5 | 技术部     |    4 | 张大鹏 → 李明 → 孙丽 → 郑秀
  3 | 王芳   |          1 | 市场部     |    2 | 张大鹏 → 王芳
  6 | 周华   |          3 | 市场部     |    3 | 张大鹏 → 王芳 → 周华
 10 | 陈杰   |          6 | 市场部     |    4 | 张大鹏 → 王芳 → 周华 → 陈杰
  7 | 周敏   |          3 | 市场部     |    3 | 张大鹏 → 王芳 → 周敏

查找所有下属

-- 查找"赵强"的所有（直接+间接）下属
WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id, 1 AS 层级
    FROM employees_hierarchy
    WHERE name = '赵强'  -- 起点

    UNION ALL

    SELECT e.id, e.name, e.manager_id, s.层级 + 1
    FROM employees_hierarchy e
    INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates ORDER BY 层级;

实战：分类层级

-- 创建商品分类表（自引用）
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INTEGER REFERENCES categories(id)
);

INSERT INTO categories (name, parent_id) VALUES
    ('电子产品', NULL),
    ('服装', NULL),
    ('家居', NULL),
    ('手机', 1),
    ('电脑', 1),
    ('耳机', 1),
    ('男装', 2),
    ('女装', 2),
    ('家具', 3),
    ('厨具', 3),
    ('智能手机', 4),
    ('笔记本电脑', 5),
    ('沙发', 9),
    ('床垫', 9),
    ('锅具', 10);

-- 递归查询：完整分类路径
WITH RECURSIVE category_tree AS (
    SELECT 
        id, 
        name, 
        parent_id, 
        name::TEXT AS full_path,
        1 AS level
    FROM categories
    WHERE parent_id IS NULL
    
    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 id, full_path, level
FROM category_tree
ORDER BY full_path;

叶子节点查询

-- 找出没有子分类的叶子节点
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, name::TEXT AS full_path
    FROM categories WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT c.id, c.name, c.parent_id, 
           ct.full_path || ' > ' || c.name
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT ct.id, ct.full_path
FROM category_tree ct
WHERE NOT EXISTS (
    SELECT 1 FROM categories c2 WHERE c2.parent_id = ct.id
)
ORDER BY ct.full_path;

CTE 修改数据

CTE 也能用在 UPDATE、DELETE、INSERT 中，实现"先查后改"的原子操作：

-- 清理从未下单的客户
WITH never_ordered AS (
    SELECT c.customer_id
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    WHERE o.order_id IS NULL
)
DELETE FROM customers
WHERE customer_id IN (SELECT customer_id FROM never_ordered)
RETURNING *;

UPSERT: 先判断是否存在

-- 如果用户存在则更新，不存在则插入
WITH upsert AS (
    INSERT INTO customers (name, city, member_level)
    VALUES ('新客户', '成都', '普通')
    ON CONFLICT (name) DO UPDATE
        SET city = EXCLUDED.city,
            member_level = EXCLUDED.member_level
    RETURNING *
)
SELECT * FROM upsert;

CTE 物化 vs 内联

PostgreSQL 12+ 中，CTE 默认是物化的（执行一次，结果复用），但也可以强制内联：

-- 默认：物化（CTE 作为一个独立单元执行一次）
WITH expensive_orders AS (
    SELECT * FROM orders WHERE total_amount > 10000
)
SELECT count(*) FROM expensive_orders
UNION ALL
SELECT count(*) FROM expensive_orders;
-- 这里 expensive_orders 只执行一次，结果被复用

-- NOT MATERIALIZED：强制内联（CTE 被嵌入到主查询中）
WITH expensive_orders AS NOT MATERIALIZED (
    SELECT * FROM orders WHERE total_amount > 10000
)
SELECT count(*) FROM expensive_orders
UNION ALL
SELECT count(*) FROM expensive_orders;
-- 这里 expensive_orders 被执行了两次

递归 CTE 的局限与优化

深度限制：默认递归深度无限制，但可以设置
```
SET max_recursive_iterations = 1000;
```
避免死循环：确保递归有终止条件
UNION vs UNION ALL：用 UNION ALL（不去重）比 UNION（会去重且排序）快得多

索引：在递归关联列上建索引，避免逐层全表扫描

CREATE INDEX idx_employees_manager ON employees_hierarchy(manager_id);

小结

CTE 让复杂查询变得像流水线一样可读。递归 CTE 是处理组织架构、分类层级、菜单树、图遍历等场景的唯一（或最佳）方式。掌握 WITH RECURSIVE，你就解锁了 SQL 在图数据处理上的全部潜力。

下一篇文章将学习视图与物化视图——如何封装复杂的查询逻辑。

Summary: CTE 拆分逻辑、递归 CTE 处理层级数据、物化 vs 内联。