Oracle with语句小结

备注:测试数据库版本为Oracle 11g R2

如需要scott用户下建表及录入数据语句，可参考:
scott建表及录入数据sql脚本

这个blog我们来聊聊Oracle with 语句
对于逻辑复杂的sql，with可以大大减少临时表的数量，提升代码的可读性、可维护性

语句结构：

with subquery_name1 as (subquery_body1),subquery_name2 as (subquery_body2)...select * from subquery_name1 a, subquery_name2 bwhere a.col = b.col....

优势
– 代码模块化
– 代码可读性增强
– 相同查询唯一化

#一.提升代码的可读性和可维护性
需求:求每个部门的平均工资,以及剔除薪资低于1000的实习人员之后的平均工资

--求每个部门的平均工资,以及剔除薪资低于1000的实习人员之后的平均工资--主查询的from后面跟了2个临时表，程序可读性不佳select d.deptno, tmp1.avg_sal avg_sal1, tmp2.avg_sal avg_sal2from dept dleft join (select e1.deptno, round(avg(nvl(e1.sal, 0)), 2) avg_salfrom emp e1group by e1.deptno) tmp1on d.deptno = tmp1.deptnoleft join (select e1.deptno, round(avg(nvl(e1.sal, 0)), 2) avg_salfrom emp e1where e1.sal > 1000group by e1.deptno) tmp2on d.deptno = tmp2.deptno;--求每个部门的平均工资,以及剔除薪资低于1000的实习人员之后的平均工资--2个临时表的定时语句通过with封装成子查询了，程序可读性增强with tmp1 as(select e1.deptno, round(avg(nvl(e1.sal, 0)), 2) avg_salfrom emp e1group by e1.deptno),tmp2 as(select e1.deptno, round(avg(nvl(e1.sal, 0)), 2) avg_salfrom emp e1where e1.sal > 1000group by e1.deptno)select d.deptno, tmp1.avg_sal avg_sal1, tmp2.avg_sal avg_sal2from dept dleft join tmp1on d.deptno = tmp1.deptnoleft join tmp2on d.deptno = tmp2.deptno;

SQL> --求每个部门的平均工资,以及剔除薪资低于1000的实习人员之后的平均工资SQL> --主查询的from后面跟了2个临时表，程序可读性不佳SQL> select d.deptno, tmp1.avg_sal avg_sal1, tmp2.avg_sal avg_sal22    from dept d3    left join (select e1.deptno, round(avg(nvl(e1.sal, 0)), 2) avg_sal4                 from emp e15                group by e1.deptno) tmp16      on d.deptno = tmp1.deptno7    left join (select e1.deptno, round(avg(nvl(e1.sal, 0)), 2) avg_sal8                 from emp e19                where e1.sal > 100010                group by e1.deptno) tmp211      on d.deptno = tmp2.deptno;DEPTNO   AVG_SAL1   AVG_SAL2------ ---------- ----------30    1566.67       169020       2175    2518.7510    2916.67    2916.6740SQL> --求每个部门的平均工资,以及剔除薪资低于1000的实习人员之后的平均工资SQL> --2个临时表的定时语句通过with封装成子查询了，程序可读性增强SQL> with tmp1 as2   (select e1.deptno, round(avg(nvl(e1.sal, 0)), 2) avg_sal3      from emp e14     group by e1.deptno),5  tmp2 as6   (select e1.deptno, round(avg(nvl(e1.sal, 0)), 2) avg_sal7      from emp e18     where e1.sal > 10009     group by e1.deptno)10  select d.deptno, tmp1.avg_sal avg_sal1, tmp2.avg_sal avg_sal211    from dept d12    left join tmp113      on d.deptno = tmp1.deptno14    left join tmp215      on d.deptno = tmp2.deptno;DEPTNO   AVG_SAL1   AVG_SAL2------ ---------- ----------30    1566.67       169020       2175    2518.7510    2916.67    2916.6740

#二.with递归
用with递归构造数列

--用with递归构造1-10的数据with c(n) as(select 1 from dual union all select n + 1 from c where n < 10)select n from c;

SQL> --用with递归构造1-10的数据SQL> with c(n) as2   (select 1 from dual union all select n + 1 from c where n < 10)3  select n from c;N----------1234567891010 rows selected

用with递归构造级联关系

with emp2(ename,empno,mgr,lvl)as(select ename, empno, mgr, 1 lvl from emp where mgr is nullunion allselect emp.ename, emp.empno, emp.mgr, e2.lvl+1from emp, emp2 e2where emp.mgr = e2.empno)select lvl,lpad(\'*\' ,2*lvl, \'*\')||ename nmfrom emp2order by lvl,ename/

SQL> with emp2(ename,empno,mgr,lvl)2    as3     (select ename, empno, mgr, 1 lvl from emp where mgr is null4      union all5      select emp.ename, emp.empno, emp.mgr, e2.lvl+16        from emp, emp2 e27       where emp.mgr = e2.empno8     )9  select lvl,10        lpad(\'*\' ,2*lvl, \'*\')||ename nm11    from emp212   order by lvl,ename13  /LVL NM---------- --------------------------------------------------------------------------------1 **KING2 ****BLAKE2 ****CLARK2 ****JONES3 ******ALLEN3 ******FORD3 ******JAMES3 ******MARTIN3 ******MILLER3 ******SCOTT3 ******TURNER3 ******WARD4 ********ADAMS4 ********SMITH14 rows selected