關於Oracle with table as的用法

最近在論壇經常看到有人使用with table as語句，一般都是構建一個暫存資料表，用於測試，經研究此語句的用法我理解有以下好處：

1）  不用實際建表，可以輕鬆構建一個暫存資料表，通過對這個表的處理測試一些功能；

例如：with t as (

select '010-82696948' telfrom dualunionall

select'020 82167684'from dualunion all

select'010-62102147\62104404'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]{7}',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