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 b
where a.col = b.col
....

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

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

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

SQL> --求每个部门的平均工资,以及剔除薪资低于1000的实习人员之后的平均工资
SQL> --主查询的from后面跟了2个临时表，程序可读性不佳
SQL> select d.deptno, tmp1.avg_sal avg_sal1, tmp2.avg_sal avg_sal2
  2    from dept d
  3    left join (select e1.deptno, round(avg(nvl(e1.sal, 0)), 2) avg_sal
  4                 from emp e1
  5                group by e1.deptno) tmp1
  6      on d.deptno = tmp1.deptno
  7    left join (select e1.deptno, round(avg(nvl(e1.sal, 0)), 2) avg_sal
  8                 from emp e1
  9                where e1.sal > 1000
 10                group by e1.deptno) tmp2
 11      on d.deptno = tmp2.deptno;
DEPTNO   AVG_SAL1   AVG_SAL2
------ ---------- ----------
    30    1566.67       1690
    20       2175    2518.75
    10    2916.67    2916.67
    40            
SQL> --求每个部门的平均工资,以及剔除薪资低于1000的实习人员之后的平均工资
SQL> --2个临时表的定时语句通过with封装成子查询了，程序可读性增强
SQL> with tmp1 as
  2   (select e1.deptno, round(avg(nvl(e1.sal, 0)), 2) avg_sal
  3      from emp e1
  4     group by e1.deptno),
  5  tmp2 as
  6   (select e1.deptno, round(avg(nvl(e1.sal, 0)), 2) avg_sal
  7      from emp e1
  8     where e1.sal > 1000
  9     group by e1.deptno)
 10  select d.deptno, tmp1.avg_sal avg_sal1, tmp2.avg_sal avg_sal2
 11    from dept d
 12    left join tmp1
 13      on d.deptno = tmp1.deptno
 14    left join tmp2
 15      on d.deptno = tmp2.deptno;
DEPTNO   AVG_SAL1   AVG_SAL2
------ ---------- ----------
    30    1566.67       1690
    20       2175    2518.75
    10    2916.67    2916.67
    40           

#二.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) as
  2   (select 1 from dual union all select n + 1 from c where n < 10)
  3  select n from c;
         N
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
10 rows selected

用with递归构造级联关系

with emp2(ename,empno,mgr,lvl)
  as
   (select ename, empno, mgr, 1 lvl from emp where mgr is null
    union all
    select emp.ename, emp.empno, emp.mgr, e2.lvl+1
      from emp, emp2 e2
     where emp.mgr = e2.empno
   )
select lvl,
      lpad('*' ,2*lvl, '*')||ename nm
  from emp2
 order by lvl,ename
/

SQL> with emp2(ename,empno,mgr,lvl)
  2    as
  3     (select ename, empno, mgr, 1 lvl from emp where mgr is null
  4      union all
  5      select emp.ename, emp.empno, emp.mgr, e2.lvl+1
  6        from emp, emp2 e2
  7       where emp.mgr = e2.empno
  8     )
  9  select lvl,
 10        lpad('*' ,2*lvl, '*')||ename nm
 11    from emp2
 12   order by lvl,ename
 13  /
       LVL NM
---------- --------------------------------------------------------------------------------
         1 **KING
         2 ****BLAKE
         2 ****CLARK
         2 ****JONES
         3 ******ALLEN
         3 ******FORD
         3 ******JAMES
         3 ******MARTIN
         3 ******MILLER
         3 ******SCOTT
         3 ******TURNER
         3 ******WARD
         4 ********ADAMS
         4 ********SMITH
14 rows selected