Oracle CTE 与递归查询（WITH 子句）

适用版本：Oracle Database 9i / 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

WITH 子句（CTE, Common Table Expression） 是 SQL 的高级特性[1]：

类型：

• 普通 CTE
• 递归 CTE（11g R2+）
• Inline CTE

---

2. 普通 CTE

2.1 基本语法

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

2.2 示例

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY dept_id
)
SELECT 
  e.last_name,
  e.salary,
  d.avg_sal,
  e.salary - d.avg_sal AS diff
FROM employees e
JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_sal;

2.3 多 CTE

WITH 
dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_sal
  FROM employees GROUP BY dept_id
),
high_paid AS (
  SELECT * FROM employees WHERE salary > 10000
)
SELECT h.last_name, h.salary, d.avg_sal
FROM high_paid h
JOIN dept_avg d ON h.dept_id = d.dept_id;

2.4 CTE 引用其他 CTE

WITH 
base AS (
  SELECT * FROM employees WHERE dept_id = 10
),
summary AS (
  SELECT dept_id, COUNT(*) AS cnt, AVG(salary) AS avg_sal
  FROM base GROUP BY dept_id
)
SELECT * FROM summary;

---

3. 递归 CTE

3.1 语法

WITH recursive_cte (columns) AS (
  -- 锚点（起点）
  SELECT ...
  
  UNION ALL
  
  -- 递归成员
  SELECT ... FROM table JOIN recursive_cte ON ...
)
SELECT * FROM recursive_cte;

3.2 组织架构示例

WITH org_chart (employee_id, last_name, manager_id, lvl, path) AS (
  -- 锚点：CEO（无经理）
  SELECT 
    employee_id,
    last_name,
    manager_id,
    1 AS lvl,
    last_name AS path
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  -- 递归：下属
  SELECT 
    e.employee_id,
    e.last_name,
    e.manager_id,
    oc.lvl + 1,
    oc.path || ' -> ' || e.last_name
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT lvl, path FROM org_chart ORDER BY path;

3.3 输出

1 | King
2 | King -> Jones
3 | King -> Jones -> Scott
4 | King -> Jones -> Scott -> Adams

---

4. 递归 CTE 应用

4.1 BOM 展开

WITH bom_tree (part_id, part_name, parent_id, qty, lvl) AS (
  -- 顶层组件
  SELECT part_id, part_name, NULL, 1, 1
  FROM parts WHERE parent_id IS NULL
  
  UNION ALL
  
  -- 子部件
  SELECT p.part_id, p.part_name, p.parent_id, p.quantity, bt.lvl + 1
  FROM parts p
  JOIN bom_tree bt ON p.parent_id = bt.part_id
)
SELECT * FROM bom_tree;

4.2 路径查找

-- 飞航线查找
WITH routes (from_city, to_city, stops, path) AS (
  -- 直飞
  SELECT from_city, to_city, 0, from_city || ' -> ' || to_city
  FROM flights
  
  UNION ALL
  
  -- 中转
  SELECT r.from_city, f.to_city, r.stops + 1, r.path || ' -> ' || f.to_city
  FROM routes r
  JOIN flights f ON r.to_city = f.from_city
  WHERE r.stops < 3  -- 最多 3 中转
)
SELECT * FROM routes WHERE from_city = 'NYC' AND to_city = 'LAX';

4.3 层级汇总

-- 部门层级汇总
WITH dept_tree (dept_id, parent_id, lvl, total_salary) AS (
  -- 顶层部门
  SELECT id, parent_id, 1, 0
  FROM departments WHERE parent_id IS NULL
  
  UNION ALL
  
  -- 子部门
  SELECT d.id, d.parent_id, dt.lvl + 1, 0
  FROM departments d
  JOIN dept_tree dt ON d.parent_id = dt.dept_id
),
dept_salaries AS (
  SELECT dept_id, SUM(salary) AS total
  FROM employees GROUP BY dept_id
)
SELECT dt.dept_id, dt.lvl, NVL(ds.total, 0) AS salary
FROM dept_tree dt
LEFT JOIN dept_salaries ds ON dt.dept_id = ds.dept_id;

---

5. CTE 优势

5.1 可读性

-- 不用 CTE（复杂子查询）
SELECT * FROM (
  SELECT * FROM (
    SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id
  ) WHERE avg_sal > 5000
) ...

-- 用 CTE（清晰）
WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id
)
SELECT * FROM dept_avg WHERE avg_sal > 5000;

5.2 复用

WITH dept_avg AS (...)
SELECT * FROM dept_avg;  -- 多次引用
SELECT * FROM dept_avg WHERE ...;

5.3 递归

• 树形结构
• 图遍历
• 层级数据

---

6. CTE 性能

6.1 内联视图 vs CTE

-- 内联视图
SELECT * FROM (SELECT ... FROM big_table) WHERE ...;

-- CTE
WITH cte AS (SELECT ... FROM big_table)
SELECT * FROM cte WHERE ...;

6.2 物化提示（12c+）

-- 强制物化
WITH cte AS (SELECT /*+ MATERIALIZE */ ... FROM big_table)
SELECT * FROM cte WHERE ...;

-- 内联（默认）
WITH cte AS (SELECT /*+ INLINE */ ... FROM big_table)
SELECT * FROM cte WHERE ...;

---

7. 递归 CTE 限制

• 不能使用 GROUP BY
• 不能使用 DISTINCT
• 不能使用聚合函数
• 不能使用 LEFT JOIN（仅 INNER）

---

8. CTE vs CONNECT BY

8.1 对比

维度	CTE	CONNECT BY
标准	ANSI	Oracle 专有
灵活	高	中
性能	中	好
路径	自定义	SYS_CONNECT_BY_PATH

8.2 示例对比

-- CONNECT BY
SELECT last_name, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

-- CTE
WITH org (id, name, mgr_id, lvl) AS (
  SELECT employee_id, last_name, manager_id, 1
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.last_name, e.manager_id, o.lvl + 1
  FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT name, lvl FROM org;

---

9. 常见坑与排错

9.1 递归无限

-- 加终止条件
WHERE r.stops < 10

9.2 性能差

-- 1. 加索引
-- 2. 限制递归深度
-- 3. 使用 MATERIALIZE 提示

9.3 列名不匹配

-- 明确列名
WITH cte (col1, col2) AS (...)

---

10. 最佳实践

1. 复杂查询用 CTE：可读性
2. 多引用用 CTE：复用
3. 层级用递归 CTE：标准
4. 限制递归深度：避免无限
5. 加索引提升：性能
6. MATERIALIZE 控制：性能
7. 命名清晰：易理解
8. 测试性能：验证

---

11. 参考资料

[1] Oracle Database SQL Language Reference 19c, “WITH Clause” https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html