with優化妙用

--語法：

/*
with
alias_name1 as (subquery1),
alias_name2 as (subQuery2),
……
alias_nameN as (subQueryN)
select col1,col2…… col3
from alias_name1,alias_name2……,alias_nameN


如：
WITH
Q1 AS (SELECT 3 + 5 S FROM DUAL),
Q2 AS (SELECT 3 * 5 M FROM DUAL),
Q3 AS (SELECT S, M, S + M, S * M FROM Q1, Q2)
SELECT * FROM Q3;

好處：
1. 結構清晰，預先定義(在本文《with自居的清晰.sql》中清晰可見。
2. 效能更好，一份複製（類似SYS_TMP...)，多份使用，可參見《with子句的高效.sql》。
3. 程式碼修改不必修改多處，可參見《with子句的高效.sql》中提到的搞笑案例。


*/


/*
查詢出部門的總薪水大於所有部門平均總薪水的部門。部門表s_dept，員工表s_emp。
分析：做這個查詢，首先必須計算出所有部門的總薪水，然後計算出總薪水的平均薪水，再篩選出部門的總薪水大於所有部門總薪水平均薪水的部門。
1. 用with 查詢查出所有部門的總薪水，
2. 用with 從第1 步獲得的結果表中查詢出平均薪水;
3. 最後利用這兩次的with 查詢比較總薪水大於平均薪水的結果，如下:
*/

--構造環境
drop table dept purge;
drop table emp purge;
create table dept as select * from scott.dept;
create table emp as select * from scott.emp;

set term off
set heading on
set verify off
set feedback off
set linesize 2000
set pagesize 30000
set long 999999999
set longchunksize 999999
set autotrace off
-------------------------------------------------------------------------------------------------------------------------------------------------------------------

WITH DEPT_COSTS AS --查詢出部門的總工資
(SELECT D.DNAME, SUM(E.SAL) DEPT_TOTAL
FROM DEPT D, EMP E
WHERE E.DEPTNO = D.DEPTNO
GROUP BY D.DNAME),
AVE_COST AS --查詢出部門的平均工資，在後一個WITH語句中可以引用前一個定義的WITH語句
(SELECT SUM(DEPT_TOTAL) / COUNT(*) AVG_SUM FROM DEPT_COSTS)
SELECT *
FROM DEPT_COSTS DC
WHERE DC.DEPT_TOTAL > (SELECT AC.AVG_SUM FROM AVE_COST AC);

DNAME DEPT_TOTAL
-------------- ----------
RESEARCH 10875

/*

可以使用前面的資料，在後面的with語句中直接引用。需求，展示根據查詢結果查詢出的資料，並把根據查詢出的結果進行統計。
如最大工資，最小工資，平均工資，進行級聯。
注意：
由於查詢的統計資料的條數為1條，所以不會發生笛卡兒積的錯誤，
*/

WITH TEMP_DATA AS --查詢基礎資料(進行級聯，由於查詢的統計資料的條數為1條，所以不會發生笛卡兒積的錯誤)
(SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM EMP),
TEM_SUM AS --查詢統計資料
(SELECT MAX(SAL), MIN(SAL), SUM(SAL) FROM TEMP_DATA)
SELECT * FROM TEM_SUM, TEMP_DATA;

MAX(SAL) MIN(SAL) SUM(SAL) EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- ---------- ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
5000 800 29025 7369 SMITH CLERK 7902 17-12月-80 800 20
5000 800 29025 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
5000 800 29025 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
5000 800 29025 7566 JONES MANAGER 7839 02-4月 -81 2975 20
5000 800 29025 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
5000 800 29025 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
5000 800 29025 7782 CLARK MANAGER 7839 09-6月 -81 2450 10
5000 800 29025 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
5000 800 29025 7839 KING PRESIDENT 17-11月-81 5000 10
5000 800 29025 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
5000 800 29025 7876 ADAMS CLERK 7788 23-5月 -87 1100 20
5000 800 29025 7900 JAMES CLERK 7698 03-12月-81 950 30
5000 800 29025 7902 FORD ANALYST 7566 03-12月-81 3000 20
5000 800 29025 7934 MILLER CLERK 7782 23-1月 -82 1300 10

--在列的子查詢中引用WITH函式:
SELECT (WITH A AS (SELECT '1' FROM DUAL) SELECT * FROM A) as COL1 FROM DUAL;

C
-
1

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29135257/viewspace-2099681/，如需轉載，請註明出處，否則將追究法律責任。

with優化妙用

相關文章