做者：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