Oracle里面，with ... as 用法介绍

在Oracle数据库中，WITH AS 子句（也称为公用表表达式，CTE, Common Table Expression）是一种在查询中定义临时结果集的方法。这个临时结果集可以在后续的查询中被引用，就像是一个临时的表或视图一样。使用 WITH AS 子句可以使复杂的查询变得更加清晰和可读。

基本语法

WITH cte_name (column1, column2, ...) AS (  -- 这里是定义CTE的SQL查询  SELECT ...  FROM ...  WHERE ...  -- 可以包含更多的SQL子句，如GROUP BY, HAVING, ORDER BY等  
)  
-- 主查询，可以引用上面定义的CTE  
SELECT ...  
FROM cte_name  
-- 可以结合其他表或CTE进行进一步查询  
JOIN ... ON ...  
WHERE ...

示例

假设我们有一个名为 employees 的表，结构如下：

CREATE TABLE employees (  employee_id NUMBER,  first_name VARCHAR2(50),  last_name VARCHAR2(50),  department_id NUMBER,  salary NUMBER  
);

我们想要查询每个部门的平均工资，并找出高于平均工资的员工。不用CET语法的sql：

SELECT e.first_name, e.last_name, e.department_id, e.salary  
FROM employees e  
JOIN (  SELECT department_id, AVG(salary) AS avg_salary  FROM employees  GROUP BY department_id  
) d_avg ON e.department_id = d_avg.department_id  
WHERE e.salary > d_avg.avg_salary;

使用CET语法的sql：

WITH department_avg_salary AS (  SELECT department_id, AVG(salary) AS avg_salary  FROM employees  GROUP BY department_id  
)  
SELECT e.first_name, e.last_name, e.department_id, e.salary  
FROM employees e  
JOIN department_avg_salary d_avg ON e.department_id = d_avg.department_id  
WHERE e.salary > d_avg.avg_salary;

优点

1. 可读性：将复杂的查询分解为多个简单的部分，使查询更容易理解。
2. 重用性：CTE可以在一个查询中被多次引用，避免重复编写相同的子查询。
3. 递归查询：CTE支持递归查询，这在处理层次结构数据时非常有用（如组织结构图、文件系统目录等）。

递归CTE示例

假设我们有一个 employees 表，其中 manager_id 列指向员工的直接上级。我们可以使用递归CTE来查找所有下属员工。

WITH RECURSIVE employee_hierarchy AS (  -- 基础部分：选择根节点（即没有上级的员工）  SELECT employee_id, first_name, last_name, manager_id, 1 AS level  FROM employees  WHERE manager_id IS NULL  UNION ALL  -- 递归部分：选择直接下属  SELECT e.employee_id, e.first_name, e.last_name, e.manager_id, eh.level + 1  FROM employees e  JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id  
)  
-- 查询结果  
SELECT * FROM employee_hierarchy;