CTE 公用表表达式

3 min中级2026/9/12

SQL公用表表达式CTE：WITH子句语法、可读性提升、多次引用、递归CTE与性能优化

1. CTE 概述

公用表表达式（Common Table Expression，CTE）是 SQL 中定义临时结果集的机制，使用 WITH 关键字定义，在后续查询中引用。

1.1 基本语法

WITH cte_name AS (
    SELECT ...
)
SELECT * FROM cte_name;

1.2 CTE vs 子查询 vs 临时表

特性	CTE	子查询	临时表
可读性	高	低（嵌套深）	高
多次引用	可以	不可以	可以
索引支持	无	无	可以创建
持久性	单条语句	单条语句	会话/事务
物化	通常不物化	可能物化	物化
递归支持	支持	不支持	不支持

2. 基本 CTE

2.1 单个 CTE

WITH dept_stats AS (
    SELECT
        dept_id,
        COUNT(*) AS emp_count,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
)
SELECT
    d.dept_name,
    ds.emp_count,
    ds.avg_salary
FROM departments d
JOIN dept_stats ds ON d.id = ds.dept_id
WHERE ds.emp_count > 10
ORDER BY ds.avg_salary DESC;

2.2 多个 CTE

WITH
dept_stats AS (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
),
high_salary_depts AS (
    SELECT dept_id FROM dept_stats WHERE avg_salary > 50000
)
SELECT e.name, e.salary, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.id
JOIN high_salary_depts hsd ON e.dept_id = hsd.dept_id
WHERE e.salary > (SELECT avg_salary FROM dept_stats WHERE dept_id = e.dept_id);

2.3 CTE 多次引用

-- CTE 可以在同一查询中多次引用
WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS total
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    m1.month,
    m1.total AS current_month,
    m2.total AS previous_month,
    m1.total - m2.total AS diff,
    ROUND((m1.total - m2.total) * 100.0 / NULLIF(m2.total, 0), 2) AS growth_rate
FROM monthly_sales m1
LEFT JOIN monthly_sales m2 ON m1.month = m2.month + INTERVAL '1 month';

3. CTE 的优势

3.1 可读性提升

-- 不使用 CTE：深层嵌套
SELECT * FROM (
    SELECT * FROM (
        SELECT dept_id, AVG(salary) AS avg_salary
        FROM employees
        GROUP BY dept_id
    ) t1
    WHERE avg_salary > 50000
) t2
ORDER BY avg_salary;

-- 使用 CTE：扁平化
WITH dept_avg AS (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
),
high_salary_depts AS (
    SELECT * FROM dept_avg WHERE avg_salary > 50000
)
SELECT * FROM high_salary_depts ORDER BY avg_salary;

3.2 逻辑分步

WITH
-- 步骤1：计算用户消费总额
user_spending AS (
    SELECT user_id, SUM(amount) AS total_spent
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id
),
-- 步骤2：划分消费等级
user_tiers AS (
    SELECT
        user_id,
        total_spent,
        CASE
            WHEN total_spent >= 10000 THEN 'platinum'
            WHEN total_spent >= 5000  THEN 'gold'
            WHEN total_spent >= 1000  THEN 'silver'
            ELSE 'bronze'
        END AS tier
    FROM user_spending
)
-- 步骤3：统计各等级人数
SELECT tier, COUNT(*) AS user_count, AVG(total_spent) AS avg_spent
FROM user_tiers
GROUP BY tier
ORDER BY avg_spent DESC;

4. CTE 的物化

4.1 默认行为

各数据库对”CTE 到底要不要物化”的策略不同，且都随版本演进过：

PostgreSQL：12 起默认内联展开（12 之前一律物化），可用 MATERIALIZED / NOT MATERIALIZED 显式控制
MySQL 8.0+：能合并（merge）时内联，不能合并时物化，无法手工干预
SQLite：引用一次时内联，多次引用时物化

-- 以下 CTE 被引用两次：PG 12+ 默认会执行两次
WITH expensive_query AS (
    SELECT * FROM large_table WHERE complex_condition
)
SELECT * FROM expensive_query WHERE flag = 'A'
UNION ALL
SELECT * FROM expensive_query WHERE flag = 'B';

WITH / WITH RECURSIVE 的支持面：PostgreSQL 8.4+、MySQL 8.0+（8.0 起才有）、SQLite 3.8.3+；MariaDB 10.2+。老版本 MySQL 5.x 完全不支持 CTE，是遗留系统迁移的常见阻碍。

4.2 PostgreSQL 物化提示

-- PostgreSQL 12+：控制 CTE 是否物化
-- MATERIALIZED：物化为临时表，只执行一次
WITH dept_stats AS MATERIALIZED (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
)
SELECT * FROM dept_stats WHERE avg_salary > 50000
UNION ALL
SELECT * FROM dept_stats WHERE avg_salary <= 50000;

-- NOT MATERIALIZED：内联展开，可能执行多次
WITH dept_stats AS NOT MATERIALIZED (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
)
SELECT * FROM dept_stats WHERE avg_salary > 50000;

4.3 何时使用物化

-- 适合物化：
-- 1. CTE 查询开销大且被多次引用
-- 2. CTE 结果集较小

-- 不适合物化：
-- 1. CTE 只被引用一次
-- 2. CTE 查询简单，内联展开后优化器可做更多优化
-- 3. CTE 结果集很大，物化占用大量内存

5. CTE 在 DML 中的使用

5.1 INSERT with CTE

WITH new_users AS (
    SELECT DISTINCT email FROM staging_data
)
INSERT INTO users (email, created_at)
SELECT email, NOW() FROM new_users
ON CONFLICT (email) DO NOTHING;

5.2 UPDATE with CTE

WITH high_value_users AS (
    SELECT user_id FROM orders
    GROUP BY user_id
    HAVING SUM(amount) > 10000
)
UPDATE users SET tier = 'vip'
WHERE id IN (SELECT user_id FROM high_value_users);

5.3 DELETE with CTE

WITH expired_sessions AS (
    SELECT id FROM sessions
    WHERE last_active < NOW() - INTERVAL '30 days'
)
DELETE FROM sessions
WHERE id IN (SELECT id FROM expired_sessions);

6. CTE 的限制

6.1 作用域限制

-- CTE 只能在定义它的查询中使用
WITH cte1 AS (SELECT 1 AS val)
SELECT * FROM cte1;
-- 以下不能引用 cte1
-- SELECT * FROM cte1;  -- 错误！

-- 多个 CTE 中，后面的可以引用前面的
WITH
cte1 AS (SELECT 1 AS val),
cte2 AS (SELECT val + 1 AS val2 FROM cte1)  -- 可以引用 cte1
SELECT * FROM cte2;

6.2 不能在 CTE 内部引用自身

-- 非 RECURSIVE CTE 不能引用自身
WITH cte1 AS (
    SELECT * FROM cte1  -- 错误！非递归 CTE 不能自引用
)
SELECT * FROM cte1;

-- 递归 CTE 使用 RECURSIVE 关键字
WITH RECURSIVE cte1 AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM cte1 WHERE n < 10
)
SELECT * FROM cte1;

基本 CTE

换行写法：定义基本 CTE WITH <CTE 名> AS (SELECT ...) SELECT ... FROM <CTE 名>

-- 定义 CTE 查询高薪员工
WITH high_paid AS (
  SELECT id, name, salary FROM employees WHERE salary > 80000
)
SELECT * FROM high_paid ORDER BY salary DESC;

换行写法：定义多个 CTE WITH <CTE 1> AS (...), <CTE 2> AS (...) SELECT ...

-- 定义多个 CTE 并联合查询
WITH
  high_paid AS (
    SELECT id, name, salary FROM employees WHERE salary > 80000
  ),
  dept_count AS (
    SELECT dept_id, COUNT(*) AS cnt FROM employees GROUP BY dept_id
  )
SELECT h.name, h.salary, d.cnt
FROM high_paid h
JOIN dept_count d ON h.dept_id = d.dept_id;

换行写法：CTE 中引用前一个 CTE WITH <CTE 1> AS (...), <CTE 2> AS (... FROM <CTE 1>) SELECT ...

-- 后一个 CTE 引用前一个 CTE
WITH
  active_users AS (
    SELECT id, name FROM users WHERE status = 'active'
  ),
  active_orders AS (
    SELECT o.* FROM orders o
    JOIN active_users au ON o.user_id = au.id
  )
SELECT * FROM active_orders;

递归 CTE

换行写法：递归 CTE 基本结构 WITH RECURSIVE <CTE 名> AS (非递归部分 UNION ALL 递归部分) SELECT ...

-- 递归 CTE 基本结构
WITH RECURSIVE counter(n) AS (
  SELECT 1          -- 非递归部分（锚点）
  UNION ALL
  SELECT n + 1     -- 递归部分
  FROM counter
  WHERE n < 10
)
SELECT * FROM counter;

换行写法：组织树形结构查询 WITH RECURSIVE <CTE 名> AS (锚点 UNION ALL 递归) SELECT ...

-- 查询员工及其所有下属（组织树）
WITH RECURSIVE org_tree AS (
  -- 锚点：顶层管理者
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- 递归：下属员工
  SELECT e.id, e.name, e.manager_id, ot.level + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY level, name;

换行写法：路径字符串拼接 WITH RECURSIVE <CTE 名> AS (... <路径列> ...) SELECT ...

-- 查询组织树并拼接路径
WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, CAST(name AS VARCHAR(1000)) AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id, ot.path || ' > ' || e.name
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT id, path FROM org_tree;

换行写法：分类树形结构查询 WITH RECURSIVE <CTE 名> AS (锚点 UNION ALL 递归) SELECT ...

-- 查询商品分类树
WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 1 AS level
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id, ct.level + 1
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY level, name;

CTE 与子查询对比

换行写法：子查询写法 SELECT ... FROM (SELECT ...) AS <别名>

-- 使用子查询查询高薪员工
SELECT * FROM (
  SELECT id, name, salary FROM employees WHERE salary > 80000
) AS high_paid
ORDER BY salary DESC;

换行写法：CTE 写法（可读性更好） WITH <CTE 名> AS (SELECT ...) SELECT ... FROM <CTE 名>

-- 使用 CTE 改写子查询
WITH high_paid AS (
  SELECT id, name, salary FROM employees WHERE salary > 80000
)
SELECT * FROM high_paid ORDER BY salary DESC;

CTE 与窗口函数

换行写法：CTE 中使用窗口函数 WITH <CTE 名> AS (SELECT ... <窗口函数> OVER (...)) SELECT ...

-- 使用 CTE 和窗口函数查询每个部门薪资前 3 名
WITH ranked_employees AS (
  SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT name, department, salary
FROM ranked_employees
WHERE rn <= 3;

CTE 在 DML 中使用

换行写法：CTE 用于 INSERT WITH <CTE 名> AS (...) INSERT INTO <表名> SELECT ... FROM <CTE 名>

-- 使用 CTE 批量插入高薪员工到奖金表
WITH high_paid AS (
  SELECT id, name, salary FROM employees WHERE salary > 80000
)
INSERT INTO bonus (employee_id, bonus_amount)
SELECT id, salary * 0.1 FROM high_paid;

换行写法：CTE 用于 UPDATE WITH <CTE 名> AS (...) UPDATE <表名> SET ... FROM <CTE 名>

-- 使用 CTE 更新员工薪资
WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY dept_id
)
UPDATE employees e
SET salary = da.avg_sal
FROM dept_avg da
WHERE e.dept_id = da.dept_id AND e.salary < da.avg_sal;

换行写法：CTE 用于 DELETE WITH <CTE 名> AS (...) DELETE FROM <表名> WHERE <列> IN (SELECT ... FROM <CTE 名>)

-- 使用 CTE 删除没有订单的客户
WITH inactive_customers AS (
  SELECT c.id FROM customers c
  LEFT JOIN orders o ON c.id = o.customer_id
  WHERE o.id IS NULL
)
DELETE FROM customers
WHERE id IN (SELECT id FROM inactive_customers);

CTE 公用表表达式

1. CTE 概述#

1.1 基本语法#

1.2 CTE vs 子查询 vs 临时表#

2. 基本 CTE#

2.1 单个 CTE#

2.2 多个 CTE#

2.3 CTE 多次引用#

3. CTE 的优势#

3.1 可读性提升#

3.2 逻辑分步#

4. CTE 的物化#

4.1 默认行为#

4.2 PostgreSQL 物化提示#

4.3 何时使用物化#

5. CTE 在 DML 中的使用#

5.1 INSERT with CTE#

5.2 UPDATE with CTE#

5.3 DELETE with CTE#

6. CTE 的限制#

6.1 作用域限制#

6.2 不能在 CTE 内部引用自身#

基本 CTE#

递归 CTE#

CTE 与子查询对比#

CTE 与窗口函数#

CTE 在 DML 中使用#