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

with as 用法

–針對(duì)一個(gè)別名

with tmp as (select * from tb_name)

–針對(duì)多個(gè)別名

with

tmp as (select * from tb_name),

tmp2 as (select * from tb_name2),

tmp3 as (select * from tb_name3),

…

–相當(dāng)于建了個(gè)e臨時(shí)表

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

select * from e;

–相當(dāng)于建了e、d臨時(shí)表

with

e as (select * from scott.emp),

d as (select * from scott.dept)

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