select mit with

Quelle: Oracle 10R2 Doku: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2129904

Subquery Factoring: Example The following statement creates the query names dept_costs and avg_cost for the initial query block containing a join, and then uses the query names in the body of the main query.

WITH
  dept_costs AS (
     SELECT department_name, SUM(salary) dept_total
        FROM employees e, departments d
        WHERE e.department_id = d.department_id
     GROUP BY department_name),
  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 department_name;