关于oracle with table as的用法
作者：iamlaosong
最远在论坛常常看到有人应用with table as语句，一样泛泛都是构建一个且则表，用于测试，经研究此语句的用法我理解有以下益处：
1） 没有效实践建表，可以也许沉松构建一个且则表，经过进程对阿谁表的措置测试一些功用；
例如：with t as (
select "010-82696948" telfrom dualunionall
select"020 82167684"from dualunion all
select"010-6210214762104404"from dualunion all
select"0860476-82321383"from dualunion all
select"020-28876096"from dualunion all
select"010-67260464-分机"from dual)
select "086-0"||regexp_replace(replace(regexp_substr(tel,"[0-9]+[- ][0-9]",1,1),"","-"),"^[0]*86[0]|^0","")from t;
--对各类花式德律风号码做典范化措置
2） 复杂的查询会产生很大的sql，with table as语法可以也许把一些公共查询提出往，也可以也许显现一个个中间成效，可以也许使整个sql语句显得有层次些，可读性进步；
3） 前面的中间成效可以也许被语句中的select或后面的中间成效表援引，相似于一个范围仅限于本语句的且则表，在需要多次查询某中间成效时可以也许提降效力 ，专程是对一些大数据量的表做多项统计时，可以也许大大进步效力。
例如：
with a as (select * from dba_objects where 某些查询条件),
b as (select * from a where 某些查询条件)
select * from b , a where 其它查询条件;
再比如：
with tb as (select * from dba_objects where 某些查询条件),
select count(*) from tb where 其它查询条件1
union
select count(*) from tb where 其它查询条件2
union
select count(*) from tb where 其它查询条件3;
1、with table as 相当于建个且则表（用于一个语句中某些中间成效放在且则表空间的SQL语句），Oracle 9i 新删WITH语法，可以也许将查询中的子查询命名，放到SELECT语句的最前面。
语法便是
with tempname as (select ....)
select ...
例子：
with t as (select * from emp where depno=10)
select * from t where empno=xxx
with wd as (select did,arg(salary) 仄均工资 from work group by did),
em as (select emp.*,w.salary from emp left join work w on emp.eid = w.eid)
select * from wd,em where wd.did =em.did and wd.仄均工资>em.salary;
2、甚么时间被覆灭
且则表没有都是会话结束便自动被PGA覆灭嘛! 但with as且则表是查询完成后便被覆灭了！
23:48:58 SCOTT@orcl>> with aa as(select * from dept)
23:57:58 2 select * from aa;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
已用年光光阴: 00: 00: 00.12
23:58:06 SCOTT@orcl>> select * from aa;
select * from aa
*
第 1 止呈现偏差:
ORA-00942: 表或视图没有存在
已用年光光阴: 00: 00: 00.02
23:58:14 SCOTT@orcl>>
3、举例
假定有张很大的表，有几年往的运营数据，数据量很大。假设要统计一段年光光阴内的邮件状况，假设都从总表中统计，效力一定没有高，而采取with tablename as 语句，先将一段年光光阴内的数据与出往，再进止统计便会简单的多。
with tb as (
select b.city,a.mail_num,a.rcv_area from tb_evt_mail_clct a, tb_jg b
where a.clct_date = to_date("20110816", "yyyymmdd")
and (a.rcv_area like "23%" or a.rcv_area like "24%")
and a.clct_bureau_org_code = b.zj_code
and not exists (select 1 from tb_evt_dlv c
where c.mail_num = a.mail_num
and c.dlv_sts_code = "I")
) -- 提与出查询数据
select aa.city 收寄都会, aa.wtt 已妥投, bb.wtd 已投递, cc.wkc 已开拆
from (select tb.city, count(*) wtt
from tb
group by tb.city) aa -- 统计1
left join (select tb.city, count(*) wtd
from tb
where not exists
(select 1 from tb_evt_dlv c
where c.mail_num = tb.mail_num
and (c.dlv_sts_code = "H" or c.dlv_sts_code = "I"))
group by tb.city) bb on bb.city = aa.city -- 统计2
left join (select tb.city, count(*) wkc
from tb
where not exists
(select 1 from tb_evt_dlv c
where c.mail_num = tb.mail_num
and (c.dlv_sts_code = "H" or c.dlv_sts_code = "I"))
and not exists
(select 1 from tb_evt_bag_mail_rela e
where e.mail_num = tb.mail_num
and e.bag_actn_code = "2"
and e.deal_org_code like
substr(tb.rcv_area, 1, 4) || "%")
group by tb.city) cc on cc.city = aa.city -- 统计3
-
由于with as是内存中的table所以还是比较快的。如果数据比较大的时候建议不要用with as这样的话变得很慢。
WITH TEMP AS (SELECT J9907003, J9907004 FROM J9907 WHERE J9907001 = '130602000000000000007541') SELECT DISTINCT 'GAZQ_J0203006' AS J9995001$, H0901001 AS J8887001$, H0901028 AS J8887014$, H0901001 AS J8887015$, H0901002 AS 姓名, (SELECT G0904004 FROM G0904 WHERE G0904002 = 'ZDXB' AND G0904003 = H0901020) AS 性别, H0901005 AS 出生日期, H0901004 AS 身份证号, H0901036 AS 建档日期, H0901026 AS 家庭住址, (SELECT G0101002 FROM G0101 WHERE G0101001 = H0901028) AS 建档机构 FROM H0901, TEMP WHERE H0901037 < TEMP.J9907004 AND H0901028 = 'R1F000000000000005116' ORDER BY 姓名; -
