with as短语,也叫做子查询部分(subquery factoring)。其实就是把一大堆重复用到的sql语句放在with as里面,取一个别名,后面的查询就可以用它,这样对于大批量的sql语句起到一个优化的作用,而且清楚明了。
当只有一个as的时候,下面的
with
a as (select * from scott.emp)
select * from a;
相当于
select * from (select * from scott.emp) a
with a as(…)相当于创建了一个临时表a,如果from里面需要用到多个临时表,则使用with as 可以方便创建,例如:
with
a as (...),
b as (...),
c as (...)
select * from a,b,c;