Oracle SQL Tricks - WITH clause

子查詢 當作 table 來用, 使用 WITH 子句, 並能重覆使用.

WITH
summary AS
(
 SELECT dname, SUM(sal) AS dept_total
 FROM emp, dept
 WHERE emp.deptno = dept.deptno
 GROUP BY dname
)
SELECT dname, dept_total
 FROM summary
  WHERE dept_total >
(
  SELECT SUM(dept_total) * 1/3
  FROM summary
)
ORDER BY dept_total DESC;