oracle中的with as语法

语法：
WITH query_name AS (subquery)
     [, query_name AS (subquery) ]...
使用在主select关键字前，oracle将其当做一个内联视图或者临时表使用。

例子：

1.   最简单的使用方法：

如查询部门名称包含“A”的所有员工信息

view source

print ?

01	--with clause

02

03

with a as

04

05	(select deptno from dept where dname like '%A%')

06

07	select * from emp where deptno in (select * from a);

08

09

with a as

10

11	(select deptno from dept where dname like '%A%'),--a结果集

12

13	a2 as(select * from a where deptno>20)--a1结果集直接从a中筛选

14

15	select * from emp where deptno in (select * from a2);

 

2.   多层同级只能用一个with，并且后面的结果集可以使用前面的结果集：

查询部门名称包含“A”并且部门编号大于20的所有员工信息

view source

print ?

1

with a as

2

3	(select deptno from dept where dname like '%A%'),--a结果集

4

5	a2 as(select * from a where deptno>20)--a1结果集直接从a中筛选

6

7	select * from emp where deptno in (select * from a2);

 

3.   不同级查询可以使用多个with：

查询部门名称包含“A”并且部门编号大于20的所有员工信息的另外一种实现方式如下

view source

print ?

01

with a as

02

03	(select deptno from dept where dname like '%A%')--a结果集

04

05	select * from emp where deptno in (--括号内层作为子查询，为第二级

06

07	with a2 as(select * from a where deptno>20)--a1结果集直接从a中筛选

08

09	select * from a2

10

11

);

 

使用场景：

那什么情况下能使用到with子句呢？以下我就举几个简单的例子，简单的说明以下：

1.         我想测试一句sql，而我不想专门建立一个测试表：

我想测试成绩大于90的学生，我不想建立学生表，可以用到with子句

view source

print ?

01	with stu as(

02

03	select '张娜' sname,99 score from dual union

04

05	select '王杰' ,35  from dual union

06

07	select '宋丽' ,85  from dual union

08

09	select '陈晓' ,73  from dual union

10

11	select '李元' ,100  from dual

12

13	)--with 组成一个临时的结果集，存放在用户的临时表空间

14

15	select  * from stu where score>90

2.         当一个sql重复用到某个相同的结果集作为子查询：

--查询销售部工资>1500或者销售部工资小于1000的员工

view source

print ?

01	select * from emp where deptno=(select deptno from dept where dname ='SALES') and sal >1500

02

03

union all

04

05	select * from emp where deptno=(select deptno from dept where dname ='SALES') and sal <1000

06

07	--以上sql select deptno from dept where dname ='SALES'需要执行两次，影响效率

08

09	--可以使用with优化一下

10

11	with salno as(select deptno from dept where dname ='SALES')

12

13	select * from emp where deptno=(select * from salno) and sal >1500

14

15

union all

16

17	select * from emp where deptno=(select * from salno) and sal <1000

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14385647/viewspace-749493/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14385647/viewspace-749493/