什么是WITH AS 语句
WITH AS相当于虚拟视图。 WITH AS也叫做分解子查询或者片段子查询。
定义一个SQL片段, 该片段会被后面的SQL语句用到,可以近似看作一个可用的临时视图。
WITH AS优点
增加了SQL的可读性,如果构造多个片段子查询,结构会更加清晰。
一次构建,多次使用,达到减少读的目标。
SYS_TEMP_XXXX便是运行过程中构造的统计结果临时表。
WITH AS的语法
WITH 临时视图名 AS (SELECT查询)
SELECT .. FROM 临时视图名;
-- 单个表临时视图WITH E AS (SELECT * FROM employees)SELECT * FROM E;-- 连接多个表的临时视图WITH e as (SELECT * FROM EMPLOYEES), d as (SELECT * FROM DEPARTMENTS)SELECT e.LAST_NAME, d.department_nameFROM e inner join dON e.department_id = d.department_id;-- 联合多个查询结果WITH e1 as (SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 100), e2 as (SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 110)SELECT * from e1UNION ALLSELECT * FROM e2;