Oracle 中 WITH 子句的使用方法

在 Oracle 数据库中，WITH 子句是一个强大且实用的工具，它能够极大地提升查询的可读性和可维护性。

WITH 子句，也被称为“公共表表达式”（CTE），允许在主查询之前定义一个临时的命名结果集。这个临时结果集可以在后续的查询中被引用，就如同它是一个实际的表一样。

使用 WITH 子句的基本语法如下：

WITH subquery_name AS (
    subquery
)
SELECT column1, column2
FROM subquery_name;

其中，subquery_name 是为临时结果集定义的名称，subquery 则是一个有效的 SQL 查询。

例如，我们有一个员工表 employees，包含员工编号、姓名、部门编号和薪资等信息。现在我们想要查询每个部门中薪资高于该部门平均薪资的员工。如果不使用 WITH 子句，查询可能会比较复杂：

SELECT e.employee_id, e.employee_name
FROM employees e
WHERE e.salary > (
    SELECT AVG(s.salary)
    FROM employees s
    WHERE s.department_id = e.department_id
);

而使用 WITH 子句，代码将变得更加清晰：

WITH dept_avg_sal AS (
    SELECT department_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department_id
)
SELECT e.employee_id, e.employee_name
FROM employees e
JOIN dept_avg_sal d
ON e.department_id = d.department_id
WHERE e.salary > d.avg_sal;

这里，我们首先在 WITH 子句中定义了一个名为 dept_avg_sal 的临时结果集，它计算了每个部门的平均薪资。然后在主查询中，我们可以轻松地使用这个临时结果集进行连接和筛选操作。

WITH 子句还支持递归查询。例如，我们有一个表示公司组织架构的表 organization，包含员工编号和上级领导编号。要查询某个员工及其所有下属，可以这样写：

WITH RECURSIVE emp_hierarchy AS (
    SELECT employee_id, manager_id
    FROM organization
    WHERE employee_id = 100  -- 假设起始员工编号为 100
    UNION ALL
    SELECT o.employee_id, o.manager_id
    FROM organization o
    INNER JOIN emp_hierarchy eh ON o.manager_id = eh.employee_id
)
SELECT employee_id
FROM emp_hierarchy;

通过递归的方式，WITH RECURSIVE 能够层层深入地获取到所有相关的数据。

Oracle 中的 WITH 子句为复杂查询提供了一种结构化的解决方案，无论是简单的临时结果集定义还是递归查询，都能让代码更加清晰易懂，从而提高开发效率和维护性。