轉載地址:http://www.cnblogs.com/scottckt/archive/2012/10/11/2719958.html

語法：

with tempName as (select ....)

select ...

例：現在要從1-19中得到11-14。一般的sql如下：

select * from

(

            --模擬生一個20行的資料

             SELECT LEVEL AS lv

               FROM DUAL

         CONNECT BY LEVEL < 20

) tt

 WHERE tt.lv > 10 AND tt.lv < 15

使用With as 的SQL為：

with TT as( --模擬生一個20行的資料 SELECT LEVEL AS lv FROM DUAL
                CONNECT BY LEVEL < 20 ) select lv from TT WHERE lv > 10 AND lv < 15

With查詢語句不是以select開始的，而是以“WITH”關鍵字開頭
可認為在真正進行查詢之前預先構造了一個臨時表TT，之後便可多次使用它做進一步的分析和處理

WITH Clause方法的優點
增加了SQL的易讀性，如果構造了多個子查詢，結構會更清晰；更重要的是：“一次分析，多次使用”，這也是為什麼會提高效能的地方，達到了“少讀”的目標。

第一種使用子查詢的方法表被掃描了兩次，而使用WITH Clause方法，表僅被掃描一次。這樣可以大大的提高資料分析和查詢的效率。

另外，觀察WITH Clause方法執行計劃，其中“SYS_TEMP_XXXX”便是在執行過程中構造的中間統計結果臨時表。

	

	
	
		 參考：http://my.oschina.net/zhangthe9/blog/28063 
	




	
	
		——寬田 著—— 
	




	



	
	
		
		
			轉載地址：  
			
				最近在論壇經常看到有人使用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

ORACLE WITH AS 用法

相關文章