WITH子句：子查询命名

WITH子句是SQL-99标准的内容，在ORACLE9.2中被引入。WITH子句可以用来命名子查询。当子查询在多个地方被使用时，可以直接使用查询名。该子句命名的子查询会被优化器当成内联视图或临时表对待。后一种情况可以提高查询效率。

 

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

SELECT e.ename AS employee_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子句，可以实现如下：

WITH dept_count AS (

  SELECT deptno, COUNT(*) AS dept_count

  FROM   emp

  GROUP BY deptno)

SELECT e.ename AS employee_name,

       dc.dept_count AS emp_dept_count

FROM   emp e,

       dept_count dc

WHERE  e.deptno = dc.deptno;

 

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

SELECT e.ename AS employee_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 employee_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_total > (SELECT avg FROM avg_cost)

ORDER BY dname;

 

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

 

综上所述，该子句主要用来简化查询，增强语句可读性，提高查询效率。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24867611/viewspace-739829/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24867611/viewspace-739829/