[转自] http://blog.csdn.net/t0nsha/article/details/6730855
为什么要用WITH？
1. 如果需要在一段复杂查询里多次应用同一个查询，用WITH可实现代码重用；
2. WITH查询类似将查询结果保留到用户临时表里，在大的复杂查询中可以减少IO，有一定的性能优化作用。
WITH查询有何限制与特性？
1. 如果当前schema下有与WITH查询别名相同的表，查询中WITH查询生成的表优先；
2. 只能用于select 语句；
3. WITH可包含一个或多个查询；
4. WITH查询可被其它查询或WITH查询引用。
示例：
- duzz$scott@orcl>select * from dept;
- DEPTNO DNAME LOC
- ---------- --------------- ----------
- 10 ACCOUNTING NEW YORK
- 20 RESEARCH DALLAS
- 30 SALES CHICAGO
- 40 OPERATIONS BOSTON
- Elapsed: 00:00:00.00
- duzz$scott@orcl>with dept as (select 1 a from dual) select * from dept;
- A
- ----------
- 1
- Elapsed: 00:00:00.00
- duzz$scott@orcl>with dept as (select 1 a from dual) delete from dept where a=1;
- with dept as (select 1 a from dual) delete from dept where a=1
- *
- ERROR at line 1:
- ORA-00928: missing SELECT keyword
- Elapsed: 00:00:00.01
- duzz$scott@orcl>with wt1 as (select 1 a, 2 b from dual), wt2 as (select 1 c,3 d from dual) select * from wt1,wt2 where wt1.a=wt2.c;
- A B C D
- ---------- ---------- ---------- ----------
- 1 2 1 3
- Elapsed: 00:00:00.00
- duzz$scott@orcl>with wt1 as (select 10 a, 2 b from dual), wt2 as (select deptno,loc from dept,wt1 where deptno=a) select loc from wt2;
- LOC
- ---------------------------------------
- NEW YORK
- Elapsed: 00:00:00.00
- duzz$scott@orcl>