oracle中with as的用法是什么

1. 什么是with as

在Oracle中，WITH AS语法结构被称为“公共表表达式”，它是一种创建临时表的方式，可以支持在查询中临时创建块，从而简化SQL编写、重用代码、提高查询性能和降低数据库开销。我们可以在with as中定义一个或多个带别名的子查询，在后续的SQL语句中使用这些子查询进行复杂的SELECT查询操作。

WITH [别名] AS (

    SELECT [列1], [列2], ... ,[列n]
    FROM [表名]
)
SELECT [列]
FROM [表名]

2. with as的使用场景

2.1 优化查询性能

对于某些需要在多个查询中被使用的子查询，可以通过with as来处理，避免多次执行相同的查询，提高查询性能。

例如，有一个含有多个子查询的SQL语句：

SELECT (SELECT COUNT(*) FROM TABLE1) AS COUNT1,

(SELECT COUNT(*) FROM TABLE2) AS COUNT2,
(SELECT COUNT(*) FROM TABLE3) AS COUNT3
FROM DUAL;

我们可以使用with as来改写这个SQL语句：

WITH T1 AS (SELECT COUNT(*) FROM TABLE1),

T2 AS (SELECT COUNT(*) FROM TABLE2),
T3 AS (SELECT COUNT(*) FROM TABLE3)
SELECT T1.*, T2.*, T3.*
FROM T1, T2, T3;

在这种改写后的SQL语句中，我们只需要在with as中定义一次子查询，在后续的查询中直接使用已经定义好的子查询，可以避免多次执行相同的查询，提高查询性能。

2.2 简化SQL编写

with as可以在查询中创建临时表，从而帮助我们减少SQL语句的复杂度，更容易理解和维护。

例如，我们需要查询一个部门的所有雇员姓名和薪资，以及部门总薪资和平均薪资，可以使用下面的SQL语句：

SELECT E.NAME, E.SAL, D.TOTAL_SAL, D.AVG_SAL

FROM EMP E, (
    SELECT DEPTNO, SUM(SAL) AS TOTAL_SAL, AVG(SAL) AS AVG_SAL
    FROM EMP
    GROUP BY DEPTNO
) D
WHERE E.DEPTNO=D.DEPTNO;

可以看到，原始的SQL语句包含了两个对EMP表的查询，嵌套语句较多。我们可以使用with as来简化这个SQL语句：

WITH EMPLOYEE AS (

    SELECT NAME, SAL, DEPTNO
    FROM EMP
),
DEPT_SALARY AS (
    SELECT DEPTNO, SUM(SAL) AS TOTAL_SAL, AVG(SAL) AS AVG_SAL
    FROM EMPLOYEE
    GROUP BY DEPTNO
)
SELECT E.NAME, E.SAL, D.TOTAL_SAL, D.AVG_SAL
FROM EMPLOYEE E, DEPT_SALARY D
WHERE E.DEPTNO=D.DEPTNO;

在优化后的SQL语句中我们定义了两个子查询，并定义了别名EMPLOYEE和DEPT_SALARY，将复杂度降低了，更加易于理解和维护。

3. 注意事项

with as可以帮助我们对SQL语句进行简化和优化，在实际使用过程中有一些需要注意的地方：

3.1 with as不能在DML语句中使用

with as只能用于SELECT语句中，不能用于INSERT、UPDATE、DELETE等DML语句中。

3.2 with as中定义的别名只在当前查询中有效

with as中定义的别名只在当前查询中有效，在下一个查询中需要重新定义使用，不能使用上一个查询中的别名。

3.3 with as中定义的子查询不能递归引用

with as中定义的子查询不能递归引用，否则会导致查询失败。

4. 总结

with as是Oracle中一种非常实用的语法结构，它可以帮助我们在查询中创建临时表，从而提高查询性能、简化SQL编写、降低数据库开销。在使用with as时，需要注意with as只能用于SELECT语句中、with as中定义的别名只在当前查询中有效、with as中定义的子查询不能递归引用等问题。通过对with as的学习和实践，可以更好地掌握Oracle的查询优化技术，提高工作效率。