Oracle查询中with as子句的用法

Oracle查询中with as子句的用法

一、with as语法

Oracle查询中的with as子句相当于创建一个临时表，将一个语句中的某些中间结果放在临时表空间。将查询中的子查询命名，放到SELECT语句的最前面。语法如下：

with temptablename([字段列表]) as (select ...)
select ...

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

with
temptablename1([字段列表]) as (select ...),
temptablename2([字段列表]) as (select ...)
select ...

说明：
（1）使用with as子句创建的临时表在检索查询完成以后就被消除。
（2）创建多个临时表时，后者可以访问前面已经建好的临时表。
（3）当一个查询有多个相同的子查询时，一般使用with as子句，因为子查询结果存在内存临时表中，执行效率较高。

二、用法举例

查询哪些部门的工资总额高于所有部门的平均工资：

1、不用with as

代码及查询结果如下：

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)
10:46:44  10        );

    DEPTNO  TOTAL_SAL
---------- ----------
	20 5701632000

Elapsed: 00:00:02.33

在该例子中，在一个查询中出现了两个相同的子查询。

2、使用with as

10:45:47 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
10:46:02  10       );

    DEPTNO  TOTAL_SAL
---------- ----------
	20 5701632000

Elapsed: 00:00:01.42

通过两种查询的对比可以看出，使用with as子句可以简化查询语句，并且可以提高查询的速度。