关于sql:with-as关键字的使用

with..as相当于一张两头表，能够简略了解为sql片段

--单个别名with aliasName as (select ....)select * from aliasName-- 多个别名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;

with as 的这种语法适宜和union all搭配应用

with temp1 as (select 'female' sex, 'zhangsan' stu_name from dual),temp2 as (select 'male' sex, 'lisi' stu_name from dual),temp3 as (select 'female' sex, 'wangwu' stu_name from dual)select * from temp1union allselect * from temp2union allselect * from temp3