当前位置：首页 > 问答 > 正文

Oracle里那个with语句到底怎么用，有啥注意点和技巧分享

黎家
问答
2026-01-14 00:41:49
4

关于Oracle里的WITH语句,很多人喜欢叫它“子查询因子化”，但说白了，它就是给一大段复杂的查询语句（特别是那些需要被重复使用的部分）提前起个名字，然后在主查询里像用一张临时表一样去引用它，这玩意儿用好了，能让你的SQL代码变得非常干净、好读，而且有时候还能让查询跑得更快。

WITH语句最基本的用法：简化代码

想象一下,你要查公司里工资比本部门平均工资高的员工，不用WITH的话，你可能得这么写：

SELECT employee_id, last_name, salary, department_id
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e1.department_id = e2.department_id
);

这个子查询 SELECT AVG(salary)... 会为外层查询的每一个员工都执行一次，虽然数据库有优化，但写起来和看起来都挺啰嗦的。

用WITH来改写,思路就清晰多了：

WITH dept_avg_sal AS (
    -- 第一步：先一次性算出每个部门的平均工资，存成一个“临时视图”
    SELECT department_id, AVG(salary) AS avg_sal
    FROM employees
    WHERE department_id IS NOT NULL
    GROUP BY department_id
)
-- 第二步：主查询直接去和这个“临时视图”连接、比较
SELECT e.employee_id, e.last_name, e.salary, e.department_id
FROM employees e
JOIN dept_avg_sal d ON e.department_id = d.department_id
WHERE e.salary > d.avg_sal;

你看,这样写是不是逻辑更清楚了？我们把计算部门平均工资这个“脏活累活”提前干完，存到dept_avg_sal这个临时结果集里，主查询就变成了一个简单的连接和过滤，代码的可读性大大提升。

WITH语句的高级用法和技巧

一个WITH里定义多个“临时表” 这是WITH语句一个非常强大的地方，你可以在一个WITH子句里，用逗号分隔，定义多个子查询，后面的子查询可以引用前面已经定义好的子查询，这在做多层数据筛选时特别有用。

你想找出公司里总工资最高的那个部门里的所有员工。

Oracle里那个with语句到底怎么用，有啥注意点和技巧分享

WITH dept_total_sal AS (
    -- 临时表1：计算每个部门的总工资
    SELECT department_id, SUM(salary) AS total_sal
    FROM employees
    WHERE department_id IS NOT NULL
    GROUP BY department_id
),
max_dept AS (
    -- 临时表2：从临时表1里，找出总工资最高的那个部门ID
    -- 注意，这里直接引用了第一个临时表 dept_total_sal
    SELECT department_id
    FROM dept_total_sal
    WHERE total_sal = (SELECT MAX(total_sal) FROM dept_total_sal)
)
-- 主查询：从员工表里，找出部门ID在max_dept里的所有员工
SELECT *
FROM employees
WHERE department_id IN (SELECT department_id FROM max_dept);

这种“分步解题”的方式，让复杂的逻辑变得像搭积木一样简单。

递归查询（CONNECT BY的现代替代品） 这是WITH语句最杀手级的应用，用来处理树形或层次结构数据，比如组织架构、菜单层级、论坛评论的楼中楼等，虽然Oracle早就有CONNECT BY语法，但用WITH的递归写法（遵循SQL标准）更强大、更灵活。

假设有张employees表，有employee_id和manager_id字段，要查出所有员工及其所有下属的层级关系。
```
WITH emp_hierarchy (employee_id, last_name, manager_id, lvl) AS (
    -- 第一部分：锚点查询，找出所有根节点（没有上级的员工，比如CEO）
    SELECT employee_id, last_name, manager_id, 1 AS lvl
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- 第二部分：递归查询，连接锚点成员和他们的下属
    SELECT e.employee_id, e.last_name, e.manager_id, eh.lvl + 1
    FROM employees e
    JOIN emp_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT employee_id, LPAD(' ', (lvl-1)*2) || last_name AS indented_name, manager_id, lvl
FROM emp_hierarchy
ORDER BY lvl, employee_id;
```
这个递归WITH子句包含两部分,用UNION ALL连接：
- 锚点成员：这是起点，找出树的最顶层节点。
- 递归成员：它引用了WITH子句自己（emp_hierarchy），通过连接条件（e.manager_id = eh.employee_id）不断地找出下一级的下属，直到找不到为止。查询结果中的lvl字段清晰地标识了每个人在组织中的层级。

重要的注意点

Oracle里那个with语句到底怎么用，有啥注意点和技巧分享

它真的是“临时视图”，不是临时表，WITH子句定义的结果集只在当前这次查询的生命周期内存在，查询一结束，它就消失了，你不能在另一个SQL会话中引用它，如果你需要真正持久化的临时对象，应该使用CREATE GLOBAL TEMPORARY TABLE。
性能不总是更好，Oracle对WITH子句有强大的优化能力，特别是当一个子查询被主查询多次引用时，数据库可能会选择将结果“物化”（Materialize），即临时存储起来，避免重复计算，这时性能提升会很明显，这并不是绝对的，对于只被引用一次的子查询，优化器可能会选择将其直接合并（Merge）到主查询中，这时性能和普通子查询没区别，甚至因为多了一层抽象，执行计划可能略有不同。不要盲目认为WITH一定更快，关键时候要看执行计划。
命名的唯一性，在同一个WITH子句里，你定义的每个“临时视图”的名字必须是唯一的，它们的列名也不能有歧义，最好使用明确的列别名。
递归查询的陷阱，写递归WITH时，必须非常小心递归成员的条件，确保它最终能停下来，如果递归条件写错了，导致无限循环，数据库会报错（Oracle会达到最大递归深度后停止，通常是几百层）。