概述

WITH AS短语，也叫做子查询部分（subquery factoring），可以做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作为提供数据的部分。

特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用WITH AS短语，则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。而提示materialize则是强制将WITH AS短语里的数据放入一个全局临时表里。

01

with as 语法

单个语法：

with tempName as (select ....)
select ...

多个语法：

with tempName1 as (select ....)，
tempName2 as (select ....)，
tempName3 as (select ....) ...
select ...

With查询语句不是以select开始的，而是以“WITH”关键字开头

可认为在真正进行查询之前预先构造了一个临时表TT，之后便可多次使用它做进一步的分析和处理。

02

WITH AS实例

例：从1-19中得到11-14。一般的sql如下：

select * from ( --模拟生一个20行的数据
SELECT LEVEL AS lv FROM DUAL CONNECT BY LEVEL < 20
) tt WHERE tt.lv > 10 AND tt.lv < 15

使用With as 的SQL为：

with TT as( SELECT LEVEL AS lv FROM DUAL CONNECT BY LEVEL < 20 ) select lv from TT WHERE lv > 10 AND lv < 15;

多个临时表实例：

WITH
T3 AS
(SELECT T1.ID, T1.CODE1, T2.DESCRIPTION
FROM TB_DATA T1, TB_CODE T2
WHERE T1.CODE1 = T2.CODE
),
T4 AS
(SELECT T1.ID, T1.CODE2, T2.DESCRIPTION
FROM TB_DATA T1, TB_CODE T2
WHERE T1.CODE2 = T2.CODE
)
SELECT T3.ID, T3.DESCRIPTION, T4.DESCRIPTION
FROM T3, T4
WHERE T3.ID = T4.ID
ORDER BY ID;

03

WITH Clause方法的优点

增加了SQL的易读性，如果构造了多个子查询，结构会更清晰；更重要的是：“一次分析，多次使用”，这也是为什么会提供性能的地方，达到了“少读”的目标。

上面第一种使用子查询的方法表被扫描了两次，而使用WITH Clause方法，表仅被扫描一次。这样可以大大的提高数据分析和查询的效率。

另外，观察WITH Clause方法执行计划，其中“SYS_TEMP_XXXX”便是在运行过程中构造的中间统计结果临时表。

04

WITH AS 与增删改查结合用法

4.1与select查询语句结合使用

查询同一个单据编号对应的借款单和核销单中，借款金额不相等的单据

with verificationInfo as
 (select ment.fnumber from shenzhenjm.t_finance_expenseremburseitem t
 left join shenzhenjm.t_finance_expenserembursement ment
 on ment.fid = t.fkrembursementid
 where 1 = 1
 group by ment.fnumber),
 
loanInfo as
 (select ment.fnumber
 from shenzhenjm.t_finance_expenseremburseitem2 t
 left join shenzhenjm.t_finance_expenserembursement ment
 on ment.fid = t.fkrembursementid
 where 1 = 1
 group by ment.fnumber)
 
select *
 from verificationInfo v, loanInfo l
 where l.fnumber = v.fnumber;

4.2与insert结合使用

如下的with as语句，不能放在insert前，而是放在紧接着要调用的地方前

insert into T_finance_ExpenseRemburseItem2
 (FID,
 FKREMBURSEMENTID,
 FAMOUNT,
 FKCREATEBYID,
 FCREATETIME,
 FKCUID,
 FKCOSTTYPEID,
 FCOSTTYPENAME)
 
 with verificationInfo as
 (select ment.fnumber,
 sum(t.famount) vLoanSum,
 ment.fnumber "单据编号",
 sum(t.famount) "核销单中借款总额"
 from shenzhenjm.t_finance_expenseremburseitem t
 left join shenzhenjm.t_finance_expenserembursement ment
 on ment.fid = t.fkrembursementid
 where 1 = 1
 group by ment.fnumber),
 
 loanInfo as
 (select ment.fnumber,
 sum(t.famount) loanSum,
 ment.fnumber "单据编号",
 sum(t.famount) "借款单中借款总额"
 from shenzhenjm.t_finance_expenseremburseitem2 t
 left join shenzhenjm.t_finance_expenserembursement ment
 on ment.fid = t.fkrembursementid
 where 1 = 1
 group by ment.fnumber)
 
 select sys_guid(),
 ment.fid,
 t.famount,
 ment.fkcreatebyid,
 ment.fcreatetime,
 ment.fkcuid,
 t.fkcosttypeid,
 t.fcosttypename
 from T_finance_ExpenseRemburseItem t
 left join t_finance_expenserembursement ment
 on ment.fid = t.fkrembursementid
 where 1 = 1
 and exists (select *
 from verificationInfo v, loanInfo l
 where l.fnumber = v.fnumber
 and l.loanSum != v.vLoanSum
 and v.fnumber = ment.fnumber);

4.3 与delete删除结合使用

delete from t_finance_expenseremburseitem2 item2
 where exists(with temp as (select t.fnumber,
 sum(item1.famount) vloanSum,
 sum(item1.frealityamount) vSum,
 sum(item2.famount) loanSum
 from t_finance_expenserembursement t
 left join t_finance_expenseremburseitem item1
 on item1.fkrembursementid = t.fid
 left join t_finance_expenseremburseitem2 item2
 on item2.fkrembursementid = t.fid
 where 1 = 1
 and t.frembursementtype = 'LOAN_REPORT'
 and to_char(t.fcreatetime, 'yyyy') > '2017'
 group by t.fnumber
 order by t.fnumber asc)
 
 select 1
 from temp t
 left join t_finance_expenserembursement ment
 on t.fnumber = ment.fnumber
 left join t_finance_expenseremburseitem2 item
 on item.fkrembursementid = ment.fid
 where t.vloanSum != t.loanSum
 and item.fid = item2.fid);

4.4与update结合使用

update dest b
 set b.NAME =
 (with t as (select * from temp)
 select a.NAME from temp a where a.ID = b.ID)

其实就是把一大堆重复用到的SQL语句放在with as 里面，取一个别名，后面的查询就可以用它，这样对于大批量的SQL语句起到一个优化的作用，而且清楚明了。后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~