oracle中的WITH子句

with 子查询
WITH子句可以用来命名子查询。当子查询在多个地方被使用时，可以直接使用查询名。该子句命名的子查询会被优化器当成内联视图或临时表对待。后一种情况可以提高查询效率。


使用SCOTT模式，对于每名员工取得他所在部门的人数，使用内联视图可以实现如下：

select e.ename as employ_name ,dc.dept_count as emp_dept_count
from emp e,(
    select deptno,count(*) as dept_count from emp 
    group by deptno) dc 
where e.deptno=dc.deptno;

使用WITH子句，可以实现如下：dept_count子句当成临时表

WITH dept_count AS(
    selct deptno,count(*) as dept_count from emp 
    group by deptno 
   )
select e.ename as employ_name,dc.dept_count as emp_dept_count
from emp e,dept_count dc
where e.deptno=dc.dpetno;

需求稍微变得复杂，现在在取得每名员工所在部门人数的同时，还要取得该员工的经理及其经理所在部门的人数。使用内联视图实现如下：
步骤：
  （1）取得每个部门的人数

    select deptno,count(*) as dept_count from emp
    group by deptno;

   (2)取得该员工的经理,及所在部门号

    (select ename,deptno
    from emp e1,emp e2
    where e1.mgr=e2.empno) kk

   (3)找出员工经理所在的部门的人数

    select count(*) as mgr_count 
    from kk
    group by deptno

    (4)汇总

    select e.ename AS employ_name,
          dc1.dept_count AS emp_dept_count,
          m.ename AS manager_name,
          dc2.dept_count AS mgr_dept_count
    from emp e,(
        select deptno,count(*) as dept_count from emp
        group by deptno) dc1,
    emp m,
      （select deptno,count(*) as dept_count from emp
        group by deptno) dc2
    where e.deptno=dc1.deptno
    and e.mgr=m.empno
    and m.deptno=dc2.deptno;

使用WITh子句实现如下：

WITh dept_count AS(
   SELECT deptno,count(*) as dept_count
   from emp
   group by deptno)
select e.ename AS employ_name,
          dc1.dept_count AS emp_dept_count,
          m.ename AS manager_name,
          dc2.dept_count AS mgr_dept_count
from emp e,
    dept_count dc1,
    emp m,
    dept_count dc2
where e.deptno=dc1.deptno
and e.mgr=m.empno
and m.deptno=dc2.deptno;

显然，使用WITH子句结构更简洁，使用内联视图时出现两次的子查询，在使用WITH子句时只出现一次。

在没有重复子查询出现的情况下，也可以使用WITH子句简化复杂查询。下面的例子列出了所有开支大于平均开支的部门。

WITH dept_costs AS(
   SELECT dname,SUM(sal) dept_total
   from emp e,dept d
   where e.deptno=d.deptno
   group by dname),
   avg_cost AS(
      SELECT SUM(dept_total)/count(*) avg
      from dept_costs)
select * from dept_costs
where dept_toal>(select avg from avg_cost)
order by dname;

上面的查询，主体部分很简单，复杂的逻辑隐藏在了WITH子句中。