我們可以使用 WITH Clause 來定義一個 query block,然後在 SELECT statement 的其它地方來使用這個 query block。如果在一個很複雜的 Query 裡,我們必須重複的使用某個 subquery,使用 WITH Clause 可以降低 Query 的複雜度以及提高 performance。WITH Clause 所讀出的資料會暫存在 User 的 temporary tablespace 中。 SQL> WITH 2 DEPT_COSTS AS ( SELECT D.department_name, SUM(E.salary) AS dept_total 3 FROM EMPLOYEES E, DEPARTMENTS D 4 WHERE E.department_id = D.department_id 5 GROUP BY D.department_name), 6 AVG_COST AS ( SELECT SUM(dept_total)/COUNT(*) AS dept_avg 7 FROM DEPT_COSTS) 8 SELECT * 9 FROM DEPT_COSTS 10 WHERE dept_total > (SELECT dept_avg FROM AVG_COST) 11 ORDER BY department_name;DEPARTMENT_NAME DEPT_TOTAL ------------------------------ ---------- Sales-- 304500 Shipping-- 156400
WITH MAX_NUMBER AS (SELECT MAX(A.orderedNumber) as numbers ,B.foodName FROM mcd_orderItems A,mdc_foodlist B WHERE B.foodNo = A.FOODNO) SELECT * FROM MAX_NUMBER;
SQL> WITH
2 DEPT_COSTS AS ( SELECT D.department_name, SUM(E.salary) AS dept_total
3 FROM EMPLOYEES E, DEPARTMENTS D
4 WHERE E.department_id = D.department_id
5 GROUP BY D.department_name),
6 AVG_COST AS ( SELECT SUM(dept_total)/COUNT(*) AS dept_avg
7 FROM DEPT_COSTS)
8 SELECT *
9 FROM DEPT_COSTS
10 WHERE dept_total > (SELECT dept_avg FROM AVG_COST)
11 ORDER BY department_name;DEPARTMENT_NAME DEPT_TOTAL
------------------------------ ----------
Sales-- 304500
Shipping-- 156400
FROM mcd_orderItems A,mdc_foodlist B
WHERE B.foodNo = A.FOODNO)
SELECT *
FROM MAX_NUMBER;