--语法：
/*
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