WITH AS短语，也叫做子查询部分（subquery factoring），可以做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。With查询语句不是以select开始的，而是以“WITH”关键字开头，可认为在真正进行查询之前预先构造了一个临时表，之后便可多次使用它做进一步的分析和处理。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作为提供数据的部分。

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

 

一、with as 语法

1 --单个语法：
2 with tempName as (select ....)
3 select ...
4 
5 --多个语法：
6 with tempName1 as (select ....)，
7 tempName2 as (select ....)，
8 tempName3 as (select ....) ...
9 select ...  

 

 

二、WITH AS实例

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

 

 

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

 

使用With as 的SQL为：

1 with TT as
2   --模拟生一个20行的数据 
3   (SELECT LEVEL AS lv FROM DUAL CONNECT BY LEVEL < 20)
4 select lv from TTWHERE lv > 10 AND lv < 15

多个临时表实例：

 1 WITH T3 AS
 2  (SELECT T1.ID, T1.CODE1, T2.DESCRIPTION
 3     FROM TB_DATA T1, TB_CODE T2
 4    WHERE T1.CODE1 = T2.CODE),
 5    
 6 T4 AS
 7  (SELECT T1.ID, T1.CODE2, T2.DESCRIPTION
 8     FROM TB_DATA T1, TB_CODE T2
 9    WHERE T1.CODE2 = T2.CODE)
10    
11 SELECT T3.ID, T3.DESCRIPTION, T4.DESCRIPTION
12   FROM T3, T4
13  WHERE T3.ID = T4.ID
14  ORDER BY ID;

 

三、WITH Clause方法的优点

 

WITH Clause方法的优点

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

2.第一种使用子查询的方法表被扫描了两次，而使用WITH Clause方法，表仅被扫描一次。这样可以大大的提高数据分析和查询的效率。另外，观察WITH Clause方法执行计划，其中“SYS_TEMP_XXXX”便是在运行过程中构造的中间统计结果临时表。

 

四、WITH AS 与增删改查结合用法

　　　注意：1.with必须紧跟引用的select语句

　　　　　　2.with创建的临时表必须被引用，否则报错

 

4.1与select查询语句结合使用

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

 1 with verificationInfo as
 2  (select ment.fnumber,
 3          sum(t.famount) vLoanSum,
 4          ment.fnumber ‘单据编号’,
 5          sum(t.famount) ‘核销单中借款总额’
 6     from shenzhenjm.t_finance_expenseremburseitem t
 7     left join shenzhenjm.t_finance_expenserembursement ment
 8       on ment.fid = t.fkrembursementid
 9    where 1 = 1
10    group by ment.fnumber),
11  
12 loanInfo as
13  (select ment.fnumber,
14          sum(t.famount) loanSum,
15          ment.fnumber "单据编号",
16          sum(t.famount) "借款单中借款总额"
17     from shenzhenjm.t_finance_expenseremburseitem2 t
18     left join shenzhenjm.t_finance_expenserembursement ment
19       on ment.fid = t.fkrembursementid
20    where 1 = 1
21    group by ment.fnumber)
22  
23 select *
24   from verificationInfo v, loanInfo l
25  where l.fnumber = v.fnumber
26    and l.loanSum != v.vLoanSum;

 

4.2与insert结合使用
如下的with as语句，不能放在insert前，而是放在紧接着要调用的地方前要求将同一个单据编号对应的借款单和核销单中，借款金额不相等的单据，对应的借款单删除，并将对应的核销单插入到借款单表中 （借款单和核销单表结构完全一样）

 1 insert into T_finance_ExpenseRemburseItem2
 2   (FID,
 3    FKREMBURSEMENTID,
 4    FAMOUNT,
 5    FKCREATEBYID,
 6    FCREATETIME,
 7    FKCUID,
 8    FKCOSTTYPEID,
 9    FCOSTTYPENAME)
10  
11   with verificationInfo as
12    (select ment.fnumber,
13            sum(t.famount) vLoanSum,
14            ment.fnumber "单据编号",
15            sum(t.famount) "核销单中借款总额"
16       from shenzhenjm.t_finance_expenseremburseitem t
17       left join shenzhenjm.t_finance_expenserembursement ment
18         on ment.fid = t.fkrembursementid
19      where 1 = 1
20      group by ment.fnumber),
21   
22   loanInfo as
23    (select ment.fnumber,
24            sum(t.famount) loanSum,
25            ment.fnumber "单据编号",
26            sum(t.famount) "借款单中借款总额"
27       from shenzhenjm.t_finance_expenseremburseitem2 t
28       left join shenzhenjm.t_finance_expenserembursement ment
29         on ment.fid = t.fkrembursementid
30      where 1 = 1
31      group by ment.fnumber)
32   
33   select sys_guid(),
34          ment.fid,
35          t.famount,
36          ment.fkcreatebyid,
37          ment.fcreatetime,
38          ment.fkcuid,
39          t.fkcosttypeid,
40          t.fcosttypename
41     from T_finance_ExpenseRemburseItem t
42     left join t_finance_expenserembursement ment
43       on ment.fid = t.fkrembursementid
44    where 1 = 1
45      and exists (select *
46             from verificationInfo v, loanInfo l
47            where l.fnumber = v.fnumber
48              and l.loanSum != v.vLoanSum
49              and v.fnumber = ment.fnumber);

 

4.3 与delete删除结合使用

 1 delete from t_finance_expenseremburseitem2 item2
 2  where exists(with temp as (select t.fnumber,
 3                              sum(item1.famount) vloanSum,
 4                              sum(item1.frealityamount) vSum,
 5                              sum(item2.famount) loanSum
 6                         from t_finance_expenserembursement t
 7                         left join t_finance_expenseremburseitem item1
 8                           on item1.fkrembursementid = t.fid
 9                         left join t_finance_expenseremburseitem2 item2
10                           on item2.fkrembursementid = t.fid
11                        where 1 = 1
12                          and t.frembursementtype = 'LOAN_REPORT'
13                          and to_char(t.fcreatetime, 'yyyy') > '2017'
14                        group by t.fnumber
15                        order by t.fnumber asc)
16  
17    select 1
18      from temp t
19      left join t_finance_expenserembursement ment
20        on t.fnumber = ment.fnumber
21      left join t_finance_expenseremburseitem2 item
22        on item.fkrembursementid = ment.fid
23     where t.vloanSum != t.loanSum
24       and item.fid = item2.fid);

 

4.4与update结合使用

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