WITH AS短語，也叫做子查詢部分（subquery factoring），可以讓你做很多事情，定義一個SQL片斷，該SQL片斷會被整個SQL語句所用到。有的時候，是為了讓SQL語句的可讀性更高些，也有可能是在UNION ALL的不同部分，作為提供數(shù)據(jù)的部分。

with as 用法

–針對一個別名

with tmp as (select * from tb_name)

–針對多個別名

with

tmp as (select * from tb_name),

tmp2 as (select * from tb_name2),

tmp3 as (select * from tb_name3),

…

–相當于建了個e臨時表

with e as (select * from scott.emp e where e.empno=7499)

select * from e;

–相當于建了e、d臨時表

with

e as (select * from scott.emp),

d as (select * from scott.dept)

select * from e, d where e.deptno = d.deptno;