Oracle语法：with...as...

with…as…的作用

如果有一个子查询sql的查询结果，在后面的查询中会多次用到（当然只用到一次也是同样可以使用with…as…），那么就可以使用with…as…语句，将子查询的结果保存到一个临时表里，后面使用的时候不需要再次去执行sql，直接从临时表里取数据就可以了。

用法

语法如下：

--针对⼀个别名的情况
with temptablename([字段列表]) as (select ...)
select ...

也可以使用with as子句定义多个临时表，创建多个临时表时，后面的临时表可以访问前面已经建好的临时表。语法如下：

--针对多个别名的情况
with
    temptablename1([字段列表]) as (select ...),
    temptablename2([字段列表]) as (select ...)
select ...

说明：

1. with…as…可以简化sql语句，增强sql语句可读性，另外也可以对⼦查询结果取有业务意义的名字等。
2. 使用with…as…子句创建的临时表在检索查询完成以后就被消除。
3. 创建多个临时表时，后者可以访问前面已经建好的临时表。
4. 当一个查询有多个相同的子查询时，一般使用with…as…子句，因为子查询结果存在内存临时表中，执行效率较高。
5. with…as…后面是接select语句，不能是更新、循环等语句。

示例

查询哪些部门的工资总额高于所有部门的平均工资。
不使用with…as…的sql语句如下：

SELECT deptno,total_sal
FROM (SELECT deptno,sum(sal) as total_sal
      FROM employee
      GROUP BY deptno) temp1
WHERE total_sal>(
      SELECT avg(total_sal)
      FROM (SELECT deptno,sum(sal) as total_sal
      FROM employee
      GROUP BY deptno)

对于上面的sql，在一个查询中出现了两个相同的子查询。

如果使用了with…as…语句，sql可以写成：

WITH tempA AS
     (SELECT deptno,sum(sal) as total_sal
      FROM employee
      GROUP BY deptno)
SELECT *
FROM tempA
WHERE total_sal>(
     SELECT AVG(total_sal)
     FROM tempA)