概述

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方面的內容，感興趣的朋友可以關注一下