WITH 子句

WITH 子句

•使 用 WITH 子句 , 可以避免在 SELECT语句中重复书写相同的语句块

• WITH 子句将该子句中的语句块执行一次并存储到用户的临时表空间中

• 使用 WITH 子句可以提高查询效率

WITH
dept_costs  AS (

   SELECT d.department_name, SUM(e.salary) AS dept_total

   FROM   employees e, departments d

   WHERE  e.department_id = d.department_id

   GROUP BY d.department_name),

avg_cost    AS (

   SELECT SUM(dept_total)/COUNT(*) AS dept_avg

   FROM  dept_costs)

SELECT*

FROM   dept_costs

WHERE  dept_total >

        (SELECT dept_avg

         FROM avg_cost)

ORDERBY department_name;