【Oracle】 with as 用法 with as语句以及与增删改查的结合使用

柔光的暖阳◎ 2022-03-30 06:51 2814阅读 0赞

[https://www.cnblogs.com/mingforyou/p/8295239.html][https_www.cnblogs.com_mingforyou_p_8295239.html]

# [纸上得来终觉浅，绝知此事要躬行][Link 1] #

# #

*  [博客园][Link 2]
 *  [首页][Link 1]
 *  [新闻][Link 3]
 *  [新随笔][Link 4]
 *  [联系][Link 5]
 *  [管理][Link 6]
 *

随笔- 536  文章- 0  评论- 120

# [ORACLE WITH AS 用法][https_www.cnblogs.com_mingforyou_p_8295239.html] #

With查询语句不是以select开始的，而是以“WITH”关键字开头  
    可认为在真正进行查询之前预先构造了一个临时表，之后便可多次使用它做进一步的分析和处理

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

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

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

语法：

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

![复制代码][copycode.gif]

--针对一个别名
    with tmp as (select * from tb_name)
    
    --针对多个别名
    with
       tmp as (select * from tb_name),
       tmp2 as (select * from tb_name2),
       tmp3 as (select * from tb_name3),
       …

![复制代码][copycode.gif]

--相当于建了个e临时表
    with e as (select * from scott.emp e where e.empno=7499)
    select * from e;
     
    --相当于建了e、d临时表
    with
         e as (select * from scott.emp),
         d as (select * from scott.dept)
    select * from e, d where e.deptno = d.deptno;

![复制代码][copycode.gif]

其实就是把一大堆重复用到的sql语句放在with as里面，取一个别名，后面的查询就可以用它，这样对于大批量的sql语句起到一个优化的作用，而且清楚明了。

向一张表插入数据的 with as 用法：

insert into table2
    with
        s1 as (select rownum c1 from dual connect by rownum <= 10),
        s2 as (select rownum c2 from dual connect by rownum <= 10)
    select a.c1, b.c2 from s1 a, s2 b where...;

with as 相当于虚拟视图。

with as短语，也叫做子查询部分(subquery factoring)，可以让你做很多事情，定义一个sql片断，该sql片断会被整个sql语句所用到。

有的时候，是为了让sql语句的可读性更高些，也有可能是在union all的不同部分，作为提供数据的部分。  
　　  
特别对于union all比较有用。

因为union all的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用with as短语，则只要执行一遍即可。

如果with as短语所定义的表名被调用两次以上，则优化器会自动将with as短语所获取的数据放入一个temp表里，如果只是被调用一次，则不会。

而提示materialize则是强制将with as短语里的数据放入一个全局临时表里。

很多查询通过这种方法都可以提高速度。

![复制代码][copycode.gif]

with
        sql1 as (select to_char(a) s_name from test_tempa),
        sql2 as (select to_char(b) s_name from test_tempb where not exists (select s_name from sql1 where rownum=1))
    select * from sql1
    union all
    select * from sql2
    union all
    select 'no records' from dual
           where not exists (select s_name from sql1 where rownum=1)
           and not exists (select s_name from sql2 where rownum=1);

![复制代码][copycode.gif]

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

分类: [Oracle][]

好文要顶 关注我 收藏该文 ![icon_weibo_24.png][] ![wechat.png][]

[![u337944.jpg_id_22102921][]][u337944.jpg_id_22102921 1]

[残星][u337944.jpg_id_22102921 1]  
[关注 - 6][- 6]  
[粉丝 - 433][- 433]

\+加关注

[« ][Link 7]上一篇：[Oracle创建Database Link][Link 7]  
[» ][Link 8]下一篇：[提示-bash: telnet: command not found的解决方法][Link 8]

posted @ 2018-01-16 10:21 [残星][Link 1] 阅读(17238) 评论(0) [编辑][Link 9] [收藏][Link 10]

刷新评论[刷新页面][Link 10][返回顶部][Link 11]

[https://www.cnblogs.com/mingforyou/p/8295239.html][https_www.cnblogs.com_mingforyou_p_8295239.html]

[https://blog.csdn.net/baidu\_37107022/article/details/79619809][https_blog.csdn.net_baidu_37107022_article_details_79619809]

原

# 深入理解和使用Oracle中with as语句以及与增删改查的结合使用 #

2018年03月20日 00:15:29 [Java仗剑走天涯][Java] 阅读数：1859更多

所属专栏： [ORACLE][]

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查询语句结合使用

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

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);`
25.

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)`

[https://blog.csdn.net/baidu\_37107022/article/details/79619809][https_blog.csdn.net_baidu_37107022_article_details_79619809]

[https://blog.csdn.net/idomyway/article/details/79037178][https_blog.csdn.net_idomyway_article_details_79037178]

原

# Oracle WITH AS 用法 #

2018年01月11日 18:15:55 [idomyway][] 阅读数：2846

### 前言 ###

可以把WITH AS 的用法看成赋值的用法，以减少SQL语句的冗余。
     当我们在SQL语句中频繁的利用某一个Select查询语句作为数据源时，我们可以用WITH AS 的用法进行简写
     增加了SQL的易读性，如果构造了多个子查询，结构会更清晰；更重要的是：“一次分析，多次使用”。

*  1
 *  2
 *  3

### 语法 ###

with tempName1 as (select ....),tempName2 as (select ....)
    select ...from  tempName

*  1
 *  2

### 例子 ###

例子：

//普通使用方法
    Select * from (
        select name ,age from stu
        union 
        select name,age from tech
        union
        …
    )
    
    //wtih as 
    with schoolPeople as (
        select name ,age from stu
        union 
        select name,age from tech
        union
        …
    )
    select * from schoolPeople

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15
 *  16
 *  17
 *  18

### 注意事项 ###

*  ① 子查询可重用相同或者前一个with查询块，通过select调用（with子句也只能被select调用）
 *  ② with子句的查询输出存储到用户临时表空间，一次查询，到处使用
 *  ③ 同级select前有多个查询定义，第一个用with，后面的不用with，并且用逗号分割
 *  ④ 最后一个with查询块与下面的select调用之间不能用逗号分割，只通过右括号分离，with子句的查询必须括号括起
 *  ⑤如果定义了with子句，而在查询中不使用，则会报ora-32035错误，只要后面有引用的即可，不一定在select调用，在后with查询块引用也是可以的
 *  ⑥ 前面的with子句定义的查询在后面的with子句中可以使用，但是一个with子句内部不能嵌套with子句
 *  ⑦ with查询的结果列有别名，引用时候必须使用别名或者\*

[https://blog.csdn.net/idomyway/article/details/79037178][https_blog.csdn.net_idomyway_article_details_79037178]

[https://www.cnblogs.com/LinXuDong/p/6077905.html][https_www.cnblogs.com_LinXuDong_p_6077905.html]

*  [博客园][Link 2]
 *  [首页][Link 12]
 *  [新随笔][Link 4]
 *  [联系][Link 13]
 *  [管理][Link 6]
 *  [订阅][Link 14][![订阅][xml.gif]][Link 14]

随笔- 5  文章- 0  评论- 1

# [Oracle with as 用法][https_www.cnblogs.com_LinXuDong_p_6077905.html] #

项目中早些时间，有个oracle查询语句写的极其复杂，因为数据量小的关系，当时也并没有怎么在意，回来随着时间过去，客户数据库数量越来越大，那段语句的劣性就体现的非常明显。优化时，发现查询关系逻辑混乱又复杂（虽然数据查询结果并没有错），多个类似的子查询嵌套，导致查询性能变的很低。

寻求资料知道ORACLE有个WITH as 用法及其好用

写法大致如下：

WITH  query1 AS

(select ...from ....where ..),

query2  AS

(select...from ...where..),

query3 AS

(select...from ...where..)

SELECT ...FROM query1,quer2,query3

where ....;

上述代码，每一个逗号(必不可少)代表一段子查询，观察执行计划发现，执行时with as 中的子查询结果会以临时表的形式存在。

这样写每段子查询相应的表仅会被检索一次，不会像原来嵌套的一样反复扫描相同的表，达到了“少读的目的”,大大提高了数据分析以及查询效率.

分类: [Oracle][Oracle 1]

好文要顶 关注我 收藏该文 ![icon_weibo_24.png][] ![wechat.png][]

[![20161118112916.png][]][20161118112916.png 1]

[Hey,Man!][20161118112916.png 1]  
[关注 - 3][- 3]  
[粉丝 - 2][- 2]

\+加关注

[« ][Link 15]上一篇：[动态加载DLL对接第三方接口][Link 15]  
[» ][Link 16]下一篇：[dev gridlookupedit][Link 16]

posted @ 2016-11-18 15:25 [Hey,Man!][Link 12] 阅读(6104) 评论(1) [编辑][Link 17] [收藏][Link 18]

[https://www.cnblogs.com/LinXuDong/p/6077905.html][https_www.cnblogs.com_LinXuDong_p_6077905.html]

[https://blog.csdn.net/huangbaokang/article/details/78474614][https_blog.csdn.net_huangbaokang_article_details_78474614]

原

# ORACLE WITH AS 用法 #

2017年11月08日 09:00:52 [黄宝康][Link 19] 阅读数：1996

**语法：**

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

*  1
 *  2

例：现在要从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

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8

**使用With as 的SQL为：**

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

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8

With查询语句不是以select开始的，而是以“WITH”关键字开头   
可认为在真正进行查询之前预先构造了一个临时表TT，之后便可多次使用它做进一步的分析和处理

**WITH Clause方法的优点**

增加了SQL的易读性，如果构造了多个子查询，结构会更清晰；更重要的是：“一次分析，多次使用”，这也是为什么会提供性能的地方，达到了“少读”的目标。
    
     第一种使用子查询的方法表被扫描了两次，而使用WITH Clause方法，表仅被扫描一次。这样可以大大的提高数据分析和查询的效率。
    
     另外，观察WITH Clause方法执行计划，其中“SYS_TEMP_XXXX”便是在运行过程中构造的中间统计结果临时表。

*  1
 *  2
 *  3
 *  4
 *  5

### 专业墙纸贴纸厨房用具装饰出售，本人网店经营 ###

博客对你有用记得访问下哦，增加下访问量，如有需要可以下单购买哦^\_^。[https://item.taobao.com/item.htm?id=569617707364][https_item.taobao.com_item.htm_id_569617707364]

[https://blog.csdn.net/huangbaokang/article/details/78474614][https_blog.csdn.net_huangbaokang_article_details_78474614]

[https://blog.csdn.net/zhu2695/article/details/79698568][https_blog.csdn.net_zhu2695_article_details_79698568]

转

# 关于oracle with table as的用法 #

2018年03月26日 15:16:16 [zhu2695][] 阅读数：687

最近在论坛经常看到有人使用with table as语句，一般都是构建一个临时表，用于测试，经研究此语句的用法我理解有以下好处：

1）  不用实际建表，可以轻松构建一个临时表，通过对这个表的处理测试一些功能；

**例如：with t as (**

**select '010-82696948' telfrom dualunionall**

**select'020 82167684'from dualunion all**

**select'010-62102147\\62104404'from dualunion all**

**select'0860476-82321383'from dualunion all**

**select'020-28876096'from dualunion all**

**select'010-67260464-分机'from dual)**

**select '086-0'||regexp\_replace(replace(regexp\_substr(tel,'\[0-9\]+\[- \]\[0-9\]\{7\}',1,1),'','-'),'^\[0\]\*86\[0\]|^0','')from t;**

\--对各种格式电话号码做规范化处理

2）  复杂的查询会产生很大的sql，with table as语法可以把一些公共查询提出来，也可以显示一个个中间结果，可以使整个sql语句显得有条理些，可读性提高；

3）  前面的中间结果可以被语句中的select或后面的中间结果表引用，类似于一个范围仅限于本语句的临时表，在需要多次查询某中间结果时可以提升效率 ，特别是对一些大数据量的表做多项统计时，可以大大提高效率。

例如：

**with a as (select \* from dba\_objects where 某些查询条件),  
     b as (select \* from a where 某些查询条件)  
     select \* from b , a  where 其它查询条件;**

**再比如：**

**with tb as (select \* from dba\_objects where 某些查询条件),  
          select count(\*) from tb  where 其它查询条件1**

**          union**

**          select count(\*) from tb  where 其它查询条件2**

**          union**

**          select count(\*) from tb  where 其它查询条件3;**

**1、语法**

with table as 相当于建个临时表（用于一个语句中某些中间结果放在临时表空间的SQL语句），Oracle 9i 新增WITH语法，可以将查询中的子查询命名，放到SELECT语句的最前面。

语法就是  
**with tempname as (select ....)  
select ...**

例子：  
**with t as (select \* from emp where depno=10)  
select \* from t where empno=xxx**

**with wd as (select did,arg(salary) 平均工资 from work group by did),  
em as (select emp.\*,w.salary from emp left join work w on emp.eid = w.eid)  
select \* from wd,em where wd.did =em.did and wd.平均工资>em.salary;**

**=============================================================**

** 注意：这个临时表只能用于查询，不能用于更新，如：**

**ith tt as ( select to\_char(sysdate,'yyyymmdd') rq from dual )  
update song\_temp set code=( select rq from tt) where code='11';**

**这个语句会报错：ora-00928: 缺失select关键字！**

**2、何时被清除**

临时表不都是会话结束就自动被PGA清除嘛! 但with as临时表是查询完成后就被清除了！  
**23:48:58 ****SCOTT@orcl****> with aa as(select \* from dept)  
23:57:58   2  select \* from aa;**

**    DEPTNO DNAME          LOC  
\---------- -------------- -------------  
        10 ACCOUNTING     NEW YORK  
        20 RESEARCH       DALLAS  
        30 SALES          CHICAGO  
        40 OPERATIONS     BOSTON**

**已用时间:  00: 00: 00.12  
23:58:06 ****SCOTT@orcl****> select \* from aa;  
select \* from aa  
              \*  
第 1 行出现错误:  
ORA-00942: 表或视图不存在**

**已用时间:  00: 00: 00.02  
23:58:14 ****SCOTT@orcl****>**

3、举例

假定有张很大的表，有几年来的经营数据，数据量很大。如果要统计一段时间内的邮件状态，如果都从总表中统计，效率一定不高，而采用with tablename as 语句，先将一段时间内的数据取出来，再进行统计就会简单的多。

**with tb as (**

**select b.city,a.mail\_num,a.rcv\_area from tb\_evt\_mail\_clct a, tb\_jg b**

**         where a.clct\_date = to\_date('20110816', 'yyyymmdd')**

**           and (a.rcv\_area like '23%' or a.rcv\_area like '24%')**

**           and a.clct\_bureau\_org\_code = b.zj\_code**

**           and not exists (select 1 from tb\_evt\_dlv c**

**                 where c.mail\_num = a.mail\_num**

**                   and c.dlv\_sts\_code = 'I')**

**                   )   -- 提取出查询数据**

**select aa.city 收寄城市, aa.wtt 未妥投, bb.wtd 未投递, cc.wkc 未开拆**

**  from (select tb.city, count(\*) wtt**

**          from tb**

**         group by tb.city) aa  -- 统计1**

**  left join (select tb.city, count(\*) wtd**

**               from tb**

**                where  not exists**

**              (select 1 from tb\_evt\_dlv c**

**                      where c.mail\_num = tb.mail\_num**

**                        and (c.dlv\_sts\_code = 'H' or c.dlv\_sts\_code = 'I'))**

**              group by tb.city) bb on bb.city = aa.city  -- 统计2**

**  left join (select tb.city, count(\*) wkc **

**               from tb**

**              where not exists**

**              (select 1  from tb\_evt\_dlv c**

**                      where c.mail\_num = tb.mail\_num**

**                        and (c.dlv\_sts\_code = 'H' or c.dlv\_sts\_code = 'I'))**

**                and not exists**

**              (select 1 from tb\_evt\_bag\_mail\_rela e**

**                      where e.mail\_num = tb.mail\_num**

**                        and e.bag\_actn\_code = '2'**

**                        and e.deal\_org\_code like**

**                            substr(tb.rcv\_area, 1, 4) || '%')**

**              group by tb.city) cc on cc.city = aa.city -- 统计3**

[https://blog.csdn.net/zhu2695/article/details/79698568][https_blog.csdn.net_zhu2695_article_details_79698568]

[https://yq.aliyun.com/ziliao/194025][https_yq.aliyun.com_ziliao_194025]

1.  云栖社区>
2.  >
3.  正文

# ORACLE WITH AS 用法 #

作者：用户 来源：互联网 时间：2018-05-18 20:37:23

[sql][][oracle][][insert][][oracle10g][][query][][Subquery][]

![ORACLE WITH AS 用法 -][ORACLE WITH AS _ -]*摘要：* 本文讲的是ORACLE WITH AS 用法， 原文传送门：http://blog.csdn.net/wh62592855/archive/2009/11/06/4776631.aspx   记得以前在论坛里看到inthirties用到过WITH AS

[教程][Link 20] [云栖大会][Link 21] [Mysql][] [备案][Link 22] [文档][Link 23] [域名][Link 24] [whois查询][whois] [PHP教程][PHP] [备份][Link 25] [互联网大学][Link 26] [云教程][Link 27]

原文传送门：http://blog.csdn.net/wh62592855/archive/2009/11/06/4776631.aspx

记得以前在论坛里看到inthirties用到过WITH AS这个字眼，当时没特别在意。今天在一个帖子里又看到有人用这个，所以就去网上搜了搜相关内容，自己小试了一把，写下来，方便以后忘了的话学习。

===================================================================================

先举个例子吧：

有两张表，分别为A、B，求得一个字段的值先在表A中寻找，如果A表中存在数据，则输出A表的值；如果A表中不存在，则在B表中寻找，若B表中有相应记录，则输出B表的值；如果B表中也不存在，则输出"no records”字符串。

with [sql][sql 1]1 as (select to\_char(a) s\_name from test\_tempa), sql2 as (select to\_char(b) s\_name from test\_tempb where not exists (select s\_name from sql1 where rownum=1)) select \* from sql1 union all select \* from sql2 union all select 'no records' from dual where not exists (select s\_name from sql1 where rownum=1) and not exists (select s\_name from sql2 where rownum=1);

再举个简单的例子

with a as (select \* from test)

select \* from a;

其实就是把一大堆重复用到的SQL语句放在with as 里面，取一个别名，后面的查询就可以用它

这样对于大批量的SQL语句起到一个优化的作用，而且清楚明了

下面是搜索到的英文[文档][Link 28]资料

About Oracle WITH clause  
Starting in Oracle9i release 2 we see an incorporation of the SQL-99 “WITH clause”, a tool for materializing subqueries to save Oracle from having to re-compute them multiple times.

The SQL “WITH clause” is very similar to the use of Global temporary tables (GTT), a technique that is often used to improve query speed for complex subqueries. Here are some important notes about the Oracle “WITH clause”:

• The SQL “WITH clause” only works on Oracle 9i release 2 and beyond.  
   • Formally, the “WITH clause” is called subquery factoring  
   • The SQL “WITH clause” is used when a subquery is executed multiple times  
   • Also useful for recursive queries (SQL-99, but not Oracle SQL)

To keep it simple, the following example only references the aggregations once, where the SQL “WITH clause” is normally used when an aggregation is referenced multiple times in a query.  
We can also use the SQL-99 “WITH clause” instead of temporary tables. The Oracle SQL “WITH clause” will compute the aggregation once, give it a name, and allow us to reference it (maybe multiple times), later in the query.

The SQL-99 “WITH clause” is very confusing at first because the SQL statement does not begin with the word SELECT. Instead, we use the “WITH clause” to start our SQL query, defining the aggregations, which can then be named in the main query as if they were “real” tables:

WITH  
subquery\_name  
AS  
(the aggregation SQL statement)  
SELECT  
(query naming subquery\_name);

Retuning to our oversimplified example, let’s replace the temporary tables with the SQL “WITH  clause”:

WITH  
sum\_sales AS  
  select /\*+ materialize \*/  
    sum(quantity) all\_sales from stores  
number\_stores AS  
  select /\*+ materialize \*/  
    count(\*) nbr\_stores from stores  
sales\_by\_store AS  
  select /\*+ materialize \*/  
  store\_name, sum(quantity) store\_sales from  
  store natural join sales  
SELECT  
   store\_name  
FROM  
   store,  
   sum\_sales,  
   number\_stores,  
   sales\_by\_store  
where  
   store\_sales > (all\_sales / nbr\_stores)  
;

Note the use of the Oracle undocumented “materialize” hint in the “WITH clause”. The Oracle materialize hint is used to ensure that the Oracle cost-based optimizer materializes the temporary tables that are created inside the “WITH” clause. This is not necessary in Oracle10g, but it helps ensure that the tables are only created one time.

It should be noted that the “WITH clause” does not yet fully-functional within Oracle SQL and it does not yet support the use of “WITH clause” replacement for “CONNECT BY” when performing recursive queries.

To see how the “WITH clause” is used in ANSI SQL-99 syntax, here is an excerpt from Jonathan Gennick’s great work “Understanding the WITH Clause” showing the use of the SQL-99 “WITH clause” to traverse a recursive bill-of-materials hierarchy  
The SQL-99 “WITH clause” is very confusing at first because the SQL statement does not begin with the word SELECT. Instead, we use the “WITH clause” to start our SQL query, defining the aggregations, which can then be named in the main query as if they were “real” tables:

WITH  
subquery\_name  
AS  
(the aggregation SQL statement)  
SELECT  
(query naming subquery\_name);

Retuning to our oversimplified example, let’s replace the temporary tables with the SQL “WITH” clause”:

=================================================================================

下面自己小试一把，当然，一点都不复杂，很简单很简单的例子，呵呵。

SQL> create table t2(id int); Table created. SQL> create table t3(id int); Table created. SQL> insert into t2 values(1); 1 row created. SQL> insert into t2 values(2); 1 row created. SQL> insert into t3 values(3); 1 row created. SQL> commit; Commit complete. SQL> select \* from t2; ID ---------- 1 2 SQL> select \* from t3; ID ---------- 3 SQL> with 2 sql1 as (select \* from t2), 3 sql2 as (select \* from t3) 4 select \* from t2 5 union 6 select \* from t3; sql2 as (select \* from t3) \* ERROR at line 3: ORA-32035: unreferenced query name defined in WITH clause --从这里可以看到，你定义了sql1和sql2，就得用它们哦，不然会报错的。 SQL> with 2 sql1 as (select \* from t2), 3 sql2 as (select \* from t3) 4 select \* from sql1 5 union 6 select \* from sql2; ID ---------- 1 2 3 --下面加个WHERE条件试试 SQL> with 2 sql1 as (select \* from t2), 3 sql2 as (select \* from t3) 4 select \* from sql1 5 union 6 select \* from sql2 7 where id in(2,3); ID ---------- 1 2 3 --奇怪。为什么加了WHERE条件还是输出ID=1的记录了，继续往下看： SQL> with 2 sql1 as (select \* from t2), 3 sql2 as (select \* from t3) 4 select \* from sql1 5 where id=3 6 union 7 select \* from sql2 8 where id=3; ID ---------- 3 --可以看到，每个条件是要针对每个SELECT语句的。

好了就先记这些吧，以后看到了新的用法再补充。

以上是ORACLE WITH AS 用法的全部内容，在云栖社区的博客、问答、云栖号、人物、课程等栏目也有ORACLE WITH AS 用法的相关内容，欢迎继续使用右上角搜索按钮进行搜索sql ， oracle ， insert ， oracle10g ， query Subquery ，以便于您获取更多的相关知识。

[上一篇][Link 29] [1][] [2][] [3][] [4][] [下一篇][Link 30]

分享到:

![qrcode_size_140_key_f24bd491b17b0517ca58bc4bc699f54d5a976d70_text_https_3A_2F_2Fyq.aliyun.com_2Fziliao_2F194025][]

### 相关文章 ###

*  [oracle while的用法示例分享\_oracle][oracle while_oracle]
 *  [oracle 下WITH CHECK OPTION用法\_Oracle应用][oracle _WITH CHECK OPTION_Oracle]
 *  [深入探讨:oracle中row\_number() over()分析函数用法\_oracle][oracle_row_number_ over_oracle]
 *  [Oracle、DB2、PostgreSQL之Sequence用法][Oracle_DB2_PostgreSQL_Sequence]
 *  [oracle解决方案\_with\_subquery=materialize或者psu(2014.07以后)][oracle_with_subquery_materialize_psu_2014.07]
 *  [深入sql oracle递归查询\_oracle][sql oracle_oracle]
 *  [oracle中config shard的状态不正常的处理][oracle_config shard]
 *  [数据从MySQL迁移到Oracle 需要注意什么\_Mysql][MySQL_Oracle _Mysql]
 *  [oracle中to\_date详细用法示例(oracle日期格式转换)\_oracle][oracle_to_date_oracle_oracle]
 *  [oracle sys\_connect\_by\_path 函数 结果集连接\_oracle][oracle sys_connect_by_path _ _oracle]

[https://yq.aliyun.com/ziliao/194025][https_yq.aliyun.com_ziliao_194025]

[https://www.cnblogs.com/sheng-jie/p/8795207.html][https_www.cnblogs.com_sheng-jie_p_8795207.html]

# [『\_\_\_知多少』][Link 31] #

## 不做调包侠！ ##

*  [博客园][Link 2]
 *  [首页][Link 31]
 *  [新随笔][Link 4]
 *  [联系][Link 32]
 *  [订阅][Link 33]
 *  [管理][Link 6]

随笔 - 110  文章 - 0  评论 - 678

# [Oracle：WITH AS () Merge ?][https_www.cnblogs.com_sheng-jie_p_8795207.html] #

WITH AS 语法在SQL SERVER 和ORACLE数据库上均支持，主要用于子查询。语法如下：

WITH expression_name [ ( column_name [,...n] ) ]
    AS
    ( CTE_query_definition )
    --只有在查询定义中为所有结果列都提供了不同的名称时，列名称列表才是可选的。
    --运行 CTE 的语句为：
    SELECT <column_list> FROM expression_name;

但其语句在两个数据库的应用却有所不同，比如在SQL SERVER 数据库上，这种语法叫做CTE，CTE后面必须直接跟使用CTE的SQL语句（如select、insert、update、merge等），否则，CTE将失效。

但是对于Oracle数据库而言，却有一个限制比较头疼，就是WITH AS后面需要紧跟SELECT语句。那如果需要执行MERGE该如何是好呢？  
简单，将MERGE 语句提前即可。

举例说明：

MERGE INTO #TEMP1 A
    USING (
    WITH SUMORDER AS 
           (SELECT PRODUCTID, SUM(AMOUNT) TOTAL 
            FROM ORDER GROUP BY PRODUCTID)
           SELECT * FROM SUMORDER
    ) B ON (A.PRODUCTID = B.PRODUCTID)
    WHEN MATCHED THEN UPDATE SET A.TOTAL = B.TOTAL;

同理，适用于其他SQL语句！

![scan-follow.bmp][]

> **关注我的公众号『微服务知多少』，我们微信不见不散。   
> 阅罢此文，如果您觉得本文不错并有所收获，请【打赏】或【推荐】，也可【评论】留下您的问题或建议与我交流。 你的支持是我不断创作和分享的不竭动力！**

作者：[『圣杰』][Link 34]

出处：[http://www.cnblogs.com/sheng-jie/][http_www.cnblogs.com_sheng-jie]

本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文链接，否则保留追究法律责任的权利。

分类: [Database][]

好文要顶 关注我 收藏该文 ![icon_weibo_24.png][] ![wechat.png][]

[![20170107145656.png][]][20170107145656.png 1]

[『圣杰』][20170107145656.png 1]  
[关注 - 76][- 76]  
[粉丝 - 656][- 656]

荣誉：[推荐博客][Link 35]

\+加关注

[« ][Link 36]上一篇：[ASP.NET Core知多少（6）：VS Code联调Angular + .NetCore][Link 36]  
[» ][Link 37]下一篇：[ASP.NET Core知多少（7）：对重复编译说NO -- dotnet watch][Link 37]

posted @ 2018-04-11 13:40 [『圣杰』][Link 31] 阅读(350) 评论(0) [编辑][Link 38] [收藏][Link 39]

刷新评论[刷新页面][Link 39][返回顶部][Link 40]

[https://www.cnblogs.com/sheng-jie/p/8795207.html][https_www.cnblogs.com_sheng-jie_p_8795207.html]

[https://wenku.baidu.com/view/434c07caf01dc281e43af034.html][https_wenku.baidu.com_view_434c07caf01dc281e43af034.html]

*  [百度文库][Link 41]
 *  [专业资料][Link 42]
 *  [IT/计算机][IT]
 *  [计算机软件及应用][Link 43]

# 关于oracle with as用法 #

[https://wenku.baidu.com/view/434c07caf01dc281e43af034.html][https_wenku.baidu.com_view_434c07caf01dc281e43af034.html]

[https_www.cnblogs.com_mingforyou_p_8295239.html]: https://www.cnblogs.com/mingforyou/p/8295239.html
[Link 1]: https://www.cnblogs.com/mingforyou/
[Link 2]: https://www.cnblogs.com/
[Link 3]: http://news.cnblogs.com/
[Link 4]: https://i.cnblogs.com/EditPosts.aspx?opt=1
[Link 5]: https://msg.cnblogs.com/send/%E6%AE%8B%E6%98%9F
[Link 6]: https://i.cnblogs.com/
[copycode.gif]: /images/20220330/1adb29d343bd4310aee41ec47629afac.png
[Oracle]: https://www.cnblogs.com/mingforyou/category/339056.html
[icon_weibo_24.png]: https://common.cnblogs.com/images/icon_weibo_24.png
[wechat.png]: /images/20220330/244ee188026d4419ae9fc64f811847c9.png
[u337944.jpg_id_22102921]: /images/20220330/04ea9f5b738347db9870ed67b2cc61f5.png
[u337944.jpg_id_22102921 1]: http://home.cnblogs.com/u/mingforyou/
[- 6]: http://home.cnblogs.com/u/mingforyou/followees
[- 433]: http://home.cnblogs.com/u/mingforyou/followers
[Link 7]: https://www.cnblogs.com/mingforyou/p/8276372.html
[Link 8]: https://www.cnblogs.com/mingforyou/p/8297721.html
[Link 9]: https://i.cnblogs.com/EditPosts.aspx?postid=8295239
[Link 10]: https://www.cnblogs.com/mingforyou/p/8295239.html#
[Link 11]: https://www.cnblogs.com/mingforyou/p/8295239.html#top
[https_blog.csdn.net_baidu_37107022_article_details_79619809]: https://blog.csdn.net/baidu_37107022/article/details/79619809
[Java]: https://me.csdn.net/baidu_37107022
[ORACLE]: https://blog.csdn.net/column/details/16319.html
[https_blog.csdn.net_idomyway_article_details_79037178]: https://blog.csdn.net/idomyway/article/details/79037178
[idomyway]: https://me.csdn.net/idomyway
[https_www.cnblogs.com_LinXuDong_p_6077905.html]: https://www.cnblogs.com/LinXuDong/p/6077905.html
[Link 12]: https://www.cnblogs.com/LinXuDong/
[Link 13]: https://msg.cnblogs.com/send/Hey%2CMan%21
[Link 14]: /images/20220330/cba15e5c18d440679be941f554b4e643.png
[xml.gif]: https://www.cnblogs.com/images/xml.gif
[Oracle 1]: https://www.cnblogs.com/LinXuDong/category/911199.html
[20161118112916.png]: /images/20220330/0feb1e09e7834af1bd257cff5bf81d9f.png
[20161118112916.png 1]: http://home.cnblogs.com/u/LinXuDong/
[- 3]: http://home.cnblogs.com/u/LinXuDong/followees
[- 2]: http://home.cnblogs.com/u/LinXuDong/followers
[Link 15]: https://www.cnblogs.com/LinXuDong/p/6077660.html
[Link 16]: https://www.cnblogs.com/LinXuDong/p/6208137.html
[Link 17]: https://i.cnblogs.com/EditPosts.aspx?postid=6077905
[Link 18]: https://www.cnblogs.com/LinXuDong/p/6077905.html#
[https_blog.csdn.net_huangbaokang_article_details_78474614]: https://blog.csdn.net/huangbaokang/article/details/78474614
[Link 19]: https://me.csdn.net/huangbaokang
[https_item.taobao.com_item.htm_id_569617707364]: https://item.taobao.com/item.htm?id=569617707364
[https_blog.csdn.net_zhu2695_article_details_79698568]: https://blog.csdn.net/zhu2695/article/details/79698568
[zhu2695]: https://me.csdn.net/zhu2695
[https_yq.aliyun.com_ziliao_194025]: https://yq.aliyun.com/ziliao/194025
[sql]: https://yq.aliyun.com/ziliao/topic_47
[oracle]: https://yq.aliyun.com/ziliao/topic_115
[insert]: https://yq.aliyun.com/ziliao/topic_550
[oracle10g]: https://yq.aliyun.com/ziliao/topic_1245
[query]: https://yq.aliyun.com/ziliao/topic_4794
[Subquery]: https://yq.aliyun.com/ziliao/topic_149203
[ORACLE WITH AS _ -]: /images/20220330/0f8cda5d75214759999a43454ab2939c.png
[Link 20]: https://www.aliyun.com/jiaocheng/
[Link 21]: https://yunqi.aliyun.com/
[Mysql]: https://yq.aliyun.com/sqlarticle
[Link 22]: https://beian.aliyun.com/
[Link 23]: https://help.aliyun.com/
[Link 24]: https://wanwang.aliyun.com/
[whois]: https://whois.aliyun.com/
[PHP]: https://yq.aliyun.com/php
[Link 25]: https://www.aliyun.com/jiaocheng/178488.html
[Link 26]: https://edu.aliyun.com/academy/VirtualAcademy
[Link 27]: https://wanwang.aliyun.com/info/
[sql 1]: https://edu.aliyun.com/jiaocheng/1043
[Link 28]: https://yq.aliyun.com/wenji
[Link 29]: https://yq.aliyun.com/ziliao/194024
[1]: https://yq.aliyun.com/ziliao/194023
[2]: https://yq.aliyun.com/ziliao/194022
[3]: https://yq.aliyun.com/ziliao/194026
[4]: https://yq.aliyun.com/ziliao/194027
[Link 30]: https://yq.aliyun.com/ziliao/194028
[qrcode_size_140_key_f24bd491b17b0517ca58bc4bc699f54d5a976d70_text_https_3A_2F_2Fyq.aliyun.com_2Fziliao_2F194025]: /images/20220330/0c167833f8ac4141b3e8c0704a6daf7e.png
[oracle while_oracle]: https://yq.aliyun.com/ziliao/119443
[oracle _WITH CHECK OPTION_Oracle]: https://yq.aliyun.com/ziliao/91069
[oracle_row_number_ over_oracle]: https://yq.aliyun.com/ziliao/126338
[Oracle_DB2_PostgreSQL_Sequence]: https://yq.aliyun.com/ziliao/42398
[oracle_with_subquery_materialize_psu_2014.07]: https://yq.aliyun.com/ziliao/23113
[sql oracle_oracle]: https://yq.aliyun.com/ziliao/126333
[oracle_config shard]: https://yq.aliyun.com/ziliao/3917
[MySQL_Oracle _Mysql]: https://yq.aliyun.com/ziliao/102888
[oracle_to_date_oracle_oracle]: https://yq.aliyun.com/ziliao/119460
[oracle sys_connect_by_path _ _oracle]: https://yq.aliyun.com/ziliao/115288?spm=a2c4e.11155472.blogcont.38.1d884d24A8Jb5P
[https_www.cnblogs.com_sheng-jie_p_8795207.html]: https://www.cnblogs.com/sheng-jie/p/8795207.html
[Link 31]: https://www.cnblogs.com/sheng-jie/
[Link 32]: https://msg.cnblogs.com/send/%E3%80%8E%E5%9C%A3%E6%9D%B0%E3%80%8F
[Link 33]: https://www.cnblogs.com/sheng-jie/rss
[scan-follow.bmp]: /images/20220330/7601a5b22a624836951c664e95c8dcd4.png
[Link 34]: http://www.jianshu.com/u/39ec0e6b1844
[http_www.cnblogs.com_sheng-jie]: http://www.cnblogs.com/sheng-jie/
[Database]: https://www.cnblogs.com/sheng-jie/category/941367.html
[20170107145656.png]: /images/20220330/04324720c787451eace0c0ef1a8da86b.png
[20170107145656.png 1]: http://home.cnblogs.com/u/sheng-jie/
[- 76]: http://home.cnblogs.com/u/sheng-jie/followees
[- 656]: http://home.cnblogs.com/u/sheng-jie/followers
[Link 35]: http://www.cnblogs.com/expert/
[Link 36]: https://www.cnblogs.com/sheng-jie/p/8655434.html
[Link 37]: https://www.cnblogs.com/sheng-jie/p/8795215.html
[Link 38]: https://i.cnblogs.com/EditPosts.aspx?postid=8795207
[Link 39]: https://www.cnblogs.com/sheng-jie/p/8795207.html#
[Link 40]: https://www.cnblogs.com/sheng-jie/p/8795207.html#top
[https_wenku.baidu.com_view_434c07caf01dc281e43af034.html]: https://wenku.baidu.com/view/434c07caf01dc281e43af034.html
[Link 41]: https://wenku.baidu.com/?fr=crumbs
[Link 42]: https://wenku.baidu.com/pro/index
[IT]: https://wenku.baidu.com/list/63
[Link 43]: https://wenku.baidu.com/list/161