WITH AS短語，也叫做子查詢部分（subquery factoring），可以做很多事情，定義一個SQL片斷，該SQL片斷會被整個SQL語句所用到。有的時候，是為了讓SQL語句的可讀性更高些，也有可能是在UNION ALL的不同部分，作為提供數據的部分。 特別對于UNION ALL比較有用。因為UNION ALL的每個部分可能相同，但是如果每個部分都去執行一遍的話，則成本太高，所以可以使用WITH AS短語，則只要執行一遍即可。如果WITH AS短語所定義的表名被調用兩次以上，則優化器會自動將WITH AS短語所獲取的數據放入一個TEMP表里，如果只是被調用一次，則不會。而提示materialize則是強制將WITH AS短語里的數據放入一個全局臨時表里。
一、with as 語法 單個語法： with?tempName?as?(select?....) select?...
多個語法： with?tempName1?as?(select?....)， tempName2?as?(select?....)， tempName3?as?(select?....) ... select?...?
With查詢語句不是以select開始的，而是以“WITH”關鍵字開頭 可認為在真正進行查詢之前預先構造了一個臨時表TT，之后便可多次使用它做進一步的分析和處理
二、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( --模擬生一個20行的數據 SELECT LEVEL AS lv FROM DUAL CONNECT BY LEVEL < 20 ) select lv from TTWHERE 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; ?
三、WITH Clause方法的優點 ???? 增加了SQL的易讀性，如果構造了多個子查詢，結構會更清晰；更重要的是：“一次分析，多次使用”，這也是為什么會提供性能的地方，達到了“少讀”的目標。 ???? 第一種使用子查詢的方法表被掃描了兩次，而使用WITH Clause方法，表僅被掃描一次。這樣可以大大的提高數據分析和查詢的效率。 ???? 另外，觀察WITH Clause方法執行計劃，其中“SYS_TEMP_XXXX”便是在運行過程中構造的中間統計結果臨時表。
四、WITH AS 與增刪改查結合用法 注意：1.with必須緊跟引用的select語句 2.with創建的臨時表必須被引用，否則報錯 4.1與select查詢語句結合使用 查詢同一個單據編號對應的借款單和核銷單中，借款金額不相等的單據 with verificationInfo as(select ment.fnumber,sum(t.famount) vLoanSum,ment.fnumber "單據編號",sum(t.famount) "核銷單中借款總額"from shenzhenjm.t_finance_expenseremburseitem tleft join shenzhenjm.t_finance_expenserembursement menton ment.fid = t.fkrembursementidwhere 1 = 1group by ment.fnumber),loanInfo as(select ment.fnumber,sum(t.famount) loanSum,ment.fnumber "單據編號",sum(t.famount) "借款單中借款總額"from shenzhenjm.t_finance_expenseremburseitem2 tleft join shenzhenjm.t_finance_expenserembursement menton ment.fid = t.fkrembursementidwhere 1 = 1group by ment.fnumber)select *from verificationInfo v, loanInfo lwhere l.fnumber = v.fnumberand l.loanSum != v.vLoanSum; 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 tleft join shenzhenjm.t_finance_expenserembursement menton ment.fid = t.fkrembursementidwhere 1 = 1group by ment.fnumber),loanInfo as(select ment.fnumber,sum(t.famount) loanSum,ment.fnumber "單據編號",sum(t.famount) "借款單中借款總額"from shenzhenjm.t_finance_expenseremburseitem2 tleft join shenzhenjm.t_finance_expenserembursement menton ment.fid = t.fkrembursementidwhere 1 = 1group by ment.fnumber)select sys_guid(),ment.fid,t.famount,ment.fkcreatebyid,ment.fcreatetime,ment.fkcuid,t.fkcosttypeid,t.fcosttypenamefrom T_finance_ExpenseRemburseItem tleft join t_finance_expenserembursement menton ment.fid = t.fkrembursementidwhere 1 = 1and exists (select *from verificationInfo v, loanInfo lwhere l.fnumber = v.fnumberand l.loanSum != v.vLoanSumand v.fnumber = ment.fnumber); 4.3 與delete刪除結合使用 delete from t_finance_expenseremburseitem2 item2where exists(with temp as (select t.fnumber,sum(item1.famount) vloanSum,sum(item1.frealityamount) vSum,sum(item2.famount) loanSumfrom t_finance_expenserembursement tleft join t_finance_expenseremburseitem item1on item1.fkrembursementid = t.fidleft join t_finance_expenseremburseitem2 item2on item2.fkrembursementid = t.fidwhere 1 = 1and t.frembursementtype = 'LOAN_REPORT'and to_char(t.fcreatetime, 'yyyy') > '2017'group by t.fnumberorder by t.fnumber asc)select 1from temp tleft join t_finance_expenserembursement menton t.fnumber = ment.fnumberleft join t_finance_expenseremburseitem2 itemon item.fkrembursementid = ment.fidwhere t.vloanSum != t.loanSumand item.fid = item2.fid);
4.4與update結合使用 update dest bset b.NAME =(with t as (select * from temp)select a.NAME from temp a where a.ID = b.ID)

總結

以上是生活随笔為你收集整理的深入理解和使用Oracle中with as语句以及与增删改查的结合使用的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。