oracle with语句

with语句可以让你给一个子查询块赋一个查询名称，然后在其他地方多次引用。oracle会将这个子查询优化成一个内部视图或者创建为一个临时表。这查询名称对主查询和所有除定义自己查询名称的子查询以外的子查询可见。

限制：
1、 在一条“单一“ SQL语句中只能有一个这样的语句

select * from scott.emp where deptno in (with tt as (select distinct deptno from scott.emp) select * from tt)
and deptno not in (with tt as (select distinct deptno from scott.emp) select * from tt)
2、在有集合操作的”子语句中“ 不能定义。例（错误）：

select * from scott.emp where deptno=30
union
(with tt as (select * from scott.emp where deptno=20),tt2 as (select * from scott.emp where deptno=30)
select * from tt)