语法：

例：现在要从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 
	




	
	

		——宽田 著—— 
	




	



	
	

		
		

			转载地址：http://www.linuxidc.com/Linux/2012-01/52108.htm  
			

				最近在论坛经常看到有人使用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