oracle with as

with

sql1 as (select to_char(a) s_name from test_tempa),
sql2 as (select to_char(b) s_name from test_tempb where not exists (select s_name from sql1 where rownum=1))
select * from sql1
union allITPUB
select * from sql2
union all
select 'no records' from dual
       where not exists (select s_name from sql1 where rownum=1)
 and not exists (select s_name from sql2 where rownum=1);

再举个简单的例子

with a as (select * from test)

select * from a;

其实就是把一大堆重复用到的SQL语句放在with as 里面，取一个别名，后面的查询就可以用它

这样对于大批量的SQL语句起到一个优化的作用，而且清楚明了

这是搜索到的英文文档资料(说得比较全,但是本人英文特菜,还没具体了解到,希望各高手具体谈谈这个with
as 的好处)