一、WITH AS简介

    Oracle在Oracle 9i的第二版本数据库中引入了带有子句的SQL；带有子句的SQL允许您给出一个子查询块一个名称（也称为子查询重构的过程），可以在主SQL查询中的多个位置中引用；该子查询的工作方式就像甲骨文的全局临时表，用于提高复杂SQL查询的查询速度。

①该子查询用于定义临时关系，以便可用此临时关系的输出，并由与子句相关联的查询使用。

②与子查询相关的查询也可以使用嵌套子查询书写，但这样做会增加更复杂的读取/调试SQL查询。

③该子查询并不支持所有的数据库系统；

④分配给子查询的名称就像是在线视图或表格一样；

⑤Oracle在Oracle 9i版本2数据库中引入了带有子句的SQL。

⑥with as临时表是查询完成后就被清除了。

⑦增加了sql的易读性，如果构造了多个子查询，结构会更清晰。

二、WITH AS子查询的语法

子查询的语法：

WITH 查询名称1 AS (子查询) ,查询名称2 AS (子查询),查询名称3 AS (子查询) 子查询定义的从句部分

三、使用场景

3.1、找到薪资超过所有员工平均工资的所有人员

①雇员表信息

② 找到薪资超过所有员工平均工资的所有人员的WITH AS子查询语句

WITH tmpTable ( avgValue ) AS ( SELECT AVG( "Salary" ) FROM "EmployeeInfo" ) 
SELECT
 * 
FROM
	"EmployeeInfo",
	tmpTable 
WHERE
	"EmployeeInfo"."Salary" > tmpTable.avgValue

说明：在此查询中，使用子查询定义一个临时关系表tmpTable，该临时表仅具有1个属性平均值avgValue【即AVG("Salary")内容】；然后在于使用查询语句对基础表的每个雇员薪资与平均值（9600）比较，只输出大于平均薪资的雇员信息。

注意：执行带有子句的查询时，首先评估子句中提到的查询，并将本评估的输出存储在临时关系中。在此之后，最终执行了将使用与子句相关联的主要查询，该查询将使用所产生的临时关系。

③查找结果 

 3.2、获取雇员的平均薪资、最高薪资、总薪资

①雇员信息表

②获取雇员的平均薪资、最高薪资和总薪资

WITH 
tmpAvgAgeTable1 AS (SELECT ROUND(AVG("Salary"), 2) avgSalary FROM "EmployeeInfo"),
tmpMaxAgeTable2 AS (SELECT MAX("Salary") maxSalary FROM "EmployeeInfo" ),
tmpSumAgeTable3 AS (SELECT SUM("Salary") sumSalary FROM "EmployeeInfo")
SELECT * FROM tmpAvgAgeTable1 t1,tmpMaxAgeTable2 t2 ,tmpSumAgeTable3 t3

③查询结果

 四、参考资料

SQL (oracle.com)https://docs.oracle.com/database/121/CNCPT/sqllangu.htm

13.2.15 WITH (Common Table Expressions) (oracle.com)https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/with.html#common-table-expressions