oracle with as 是什么并且怎么用

Oracle中的WITH AS语句，也被称为Common Table Expressions（CTE），是一个用于定义临时结果集或视图的子句。这个临时结果集或视图只在当前的查询中存在，并且在查询完成后会被自动删除。使用WITH AS可以提高SQL语句的可读性，并且可以优化查询性能，特别是在处理复杂的子查询时。

基本语法

WITH 临时视图名 AS (  SELECT ... FROM ... WHERE ...  
)  
SELECT ... FROM 临时视图名 ...;

用法

1. 简单使用：
   假设我们有一个名为employees的表，我们想要从中选择前10个员工的信息，并对结果进行排序。使用WITH AS可以简化查询语句：

WITH top_employees AS (  SELECT * FROM employees WHERE ROWNUM <= 10  
)  
SELECT * FROM top_employees ORDER BY last_name;

1. 多个CTE：
   可以在一个查询中定义多个CTE，并且后面的CTE可以引用前面已经定义的CTE。

WITH   emp_dept AS (  SELECT department_id, AVG(salary) AS avg_salary  FROM employees  GROUP BY department_id  ),  high_paid_depts AS (  SELECT department_id  FROM emp_dept  WHERE avg_salary > 5000  )  
SELECT d.department_name  
FROM departments d  
JOIN high_paid_depts h ON d.department_id = h.department_id;

1. 递归CTE：
   用于处理具有层次结构或递归关系的数据。

WITH RECURSIVE emp_hierarchy (employee_id, employee_name, manager_id, level) AS (  SELECT employee_id, employee_name, manager_id, 1 AS level  FROM employees  WHERE manager_id IS NULL -- 顶层员工  UNION ALL  SELECT e.employee_id, e.employee_name, e.manager_id, eh.level + 1  FROM employees e  JOIN emp_hierarchy eh ON e.manager_id = eh.employee_id  
)  
SELECT * FROM emp_hierarchy;

优点

• 提高可读性：通过将复杂的子查询分解为多个CTE，可以使查询结构更加清晰。
• 优化性能：CTE的结果集可以存储在内存中，当多次引用该结果集时，可以避免重复计算。
• 灵活性：CTE可以在SELECT、INSERT、UPDATE和DELETE语句中使用，并且可以在一个CTE中引用另一个CTE。

总的来说，WITH AS语句在Oracle中是一个强大的工具，可以帮助我们更好地组织和管理复杂的SQL查询。