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查询。