with table as 相当于建个临时表,将一个语句中某些中间结果放在临时表空间的SQL语句,可以将查询中的子查询命名,放到SELECT语句的最前面,此语法从Oracle 9i开始新增。
(1)、基本语法格式:
with temptablename as (select ....)
select ... from temptablename
(2)、多个临时表之间用『,』分开,格式如下:
with
a as (select did,arg(salary) 平均工资 from work group by did),
b as (select emp.*,w.salary from emp left join work w on emp.eid = w.eid)
select * from a,b where wd.did =em.did and wd.平均工资>em.salary;
(3)、建立临时表的时候,默认使用检索出来的字段名作为临时表字段名,也可以自己定义临时表里面的字段名,比如:
with t_a (column1) as (select count(*) a from t_1),
t_b (column2) as (select count(*) b from t_2),
t_c (column3) as (select count(*) c from t_3)
select * from t_a, t_b, t_c where column1 = column2 and column2 = column3
注意点:
(1)、 此种语法创建的临时表跟create temporary table as (select ... from ... where ...) 创建的临时表不一样,后者是会话结束就自动被消除,前者是检索查询完成以后就被消除。
(2)、建立多个临时表的时候,后者是可以访问前面已经建好的临时表的。
(3)、从功能上讲,跟子查询一样的效果。但是执行计划不同,当有多个子查询的时候,特别是相同子查询,一般用with写这部分,因为子查询结果存在内存临时表中,执行效率当然也就会高很多。