精华内容
下载资源
问答
  • ... 纸上得来终觉浅,绝知此事要躬行   博客园 首页 新闻 新随笔 联系 ...ORACLE WITH AS 用法 With查询语句不是以select开始的,而是以“WITH”关键字开头  可认为在真正进行查询之前预...

     

    https://www.cnblogs.com/mingforyou/p/8295239.html

    纸上得来终觉浅,绝知此事要躬行

     

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

    ORACLE WITH AS 用法

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

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

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

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

     

    语法:

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

     

    复制代码

    --针对一个别名
    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),
       …

    复制代码

     

    复制代码

    --相当于建了个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;

    复制代码

     

    其实就是把一大堆重复用到的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短语里的数据放入一个全局临时表里。

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

    复制代码

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

    复制代码

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

     

    分类: Oracle

    好文要顶 关注我 收藏该文  

    残星
    关注 - 6
    粉丝 - 433

    +加关注

    1

    0

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

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

    刷新评论刷新页面返回顶部

    https://www.cnblogs.com/mingforyou/p/8295239.html

     

     

     

     

     

     

    https://blog.csdn.net/baidu_37107022/article/details/79619809

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

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

    所属专栏: ORACLE

     版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/baidu_37107022/article/details/79619809

    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/idomyway/article/details/79037178

    Oracle WITH AS 用法

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

     版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/idomyway/article/details/79037178

    前言

     可以把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://www.cnblogs.com/LinXuDong/p/6077905.html

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

    Oracle with as 用法

      项目中早些时间,有个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

    好文要顶 关注我 收藏该文  

    Hey,Man!
    关注 - 3
    粉丝 - 2

    +加关注

    0

    0

    « 上一篇:动态加载DLL对接第三方接口
    » 下一篇:dev gridlookupedit

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

    https://www.cnblogs.com/LinXuDong/p/6077905.html

     

     

     

     

     

     

    https://blog.csdn.net/huangbaokang/article/details/78474614

    ORACLE WITH AS 用法

    2017年11月08日 09:00:52 黄宝康 阅读数: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://blog.csdn.net/huangbaokang/article/details/78474614

     

     

     

     

     

     

    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://yq.aliyun.com/ziliao/194025

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

    ORACLE WITH AS 用法

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

    sqloracleinsertoracle10gquerySubquery

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

    教程 云栖大会 Mysql 备案 文档 域名 whois查询 PHP教程 备份 互联网大学 云教程

    原文传送门: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 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);

    再举个简单的例子

    with a as (select * from test)

    select * from a;

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

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

    下面是搜索到的英文文档资料

    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 ,以便于您获取更多的相关知识。

    上一篇 1 2 3 4 下一篇

    分享到:

    相关文章

    https://yq.aliyun.com/ziliao/194025

     

     

     

     

     

     

    https://www.cnblogs.com/sheng-jie/p/8795207.html

    『___知多少』

    不做调包侠!

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

    Oracle:WITH AS () Merge ?

    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语句!

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

    作者:『圣杰』

    出处:http://www.cnblogs.com/sheng-jie/

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

    分类: Database

    好文要顶 关注我 收藏该文  

    『圣杰』
    关注 - 76
    粉丝 - 656

    荣誉:推荐博客

    +加关注

    0

    0

    « 上一篇:ASP.NET Core知多少(6):VS Code联调Angular + .NetCore
    » 下一篇:ASP.NET Core知多少(7):对重复编译说NO -- dotnet watch

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

    刷新评论刷新页面返回顶部

    https://www.cnblogs.com/sheng-jie/p/8795207.html

     

     

     

     

     

    https://wenku.baidu.com/view/434c07caf01dc281e43af034.html

    关于oracle with as用法

    https://wenku.baidu.com/view/434c07caf01dc281e43af034.html

     

     

     

     

     

     

     

     

     

     

     

     

     

    展开全文
  • in which可以翻译成在……里面for which可以翻译成为了……目的on which可以翻译成在……的上面,或具体时间的某一天at which可以翻译成在……里面或在……上面这些介词的使用除了意思上的区别,具体是要以which...

    这些都是定语从句里面,由which引导的定语从句,介词提前。

    in which可以翻译成在……里面

    for which可以翻译成为了……目的

    on which可以翻译成在……的上面,或具体时间的某一天

    at which可以翻译成在……里面或在……上面

    这些介词的使用除了意思上的区别,具体是要以which引导的从句而定的。

    例如: 

    (1) The school (that/which) he once studied in is very famous.

    = The school in which he once studied is very famous.

    (2) Tomorrow I will bring here a magazine (that/which) you asked for

    = Tomorrow I will bring here a magazine for which you asked.

    (3) I have a dresser in my bedroom, which there are many cosmetics on.

    = I have a dresser in my bedroom on which there are many cosmetics.

    (4)The dumpling House which he often has dumplings at is very popular.

    = The dumpling House is very popular, at which he often has dumplings.

    当然这只是介词作为基本意思的用法,还有一些固定搭配,得具体情况具体分析。

    例如:

    The plane may be several hours late, in which case theres no point in our waiting.

    (固定搭配:in the case)

    The speaker paused to examine his notes, at which point a loud crash was heard.

    (固定搭配:at the point)

    Ten years of hard work changed her greatly, for which reason he could hardly recognize her at first sight.(固定搭配:for the reason

    The film at which I fell asleep was very boring.

    Here "at which" is based on "fell asleep at the film"因为电影无聊而睡觉。)

    The little creature in which scientists are interested is known as ET.

    (这里"be interested in"是词组原因)

    This is the book for which he is looking.

    (即等于This is the book which he is looking for,这主要是因为look for 这一短语)

    例题:1. I can’t believe that the restaurant, ______ I have eaten such wonderful meal, is going to be close down. 

    Awhere    Bat which    Cabout which

    【解答】选B。这个是非限制性定语从句,at which在功能上等于where,但就本题而言只能选at which。因为at which I have eaten such wonderful meal是一个插入语,where固然可以引导,但是由于前面有了一个逗号,就只能用at which了。“我难以相信我曾经在里面美美地吃过几顿大餐的饭店就要关张了。”非限制性定语从句主要就是先行词+逗号“,”+介词+which或者是先行词+逗号“,”+which,它和限定性定语从句的最显著区别一个是有逗号,一个是非限制定语从句总有which

    2. Wind power is an ancient source of energy ______ we may return in near future.

    A. on which    B. by which   C. to which   D. from which

    【解答】选Creturn to其实,应该来说,就是一个固定搭配了,解释为回归到,但是呢,如果要说意思的话,也是说得通的,就是把句子换一换语序你就可以看出来了。“we may return to wind power in near future.”也就是说,看到prep.+ which这种形式,就可以直接把它扔到动词后面,然后在进行选择,看缺少哪个介词。

    3. We have gathered nearly 100,000 quilts, _____up to half have gone to flood-hit areas.

    A. with which   B. in which   C. for which   D. of which    

    【解答】选D。这里的which指的是,nearly 100,000 quilts(被褥),也就是说,后半句的主语是在这nearly 100,000 quilts里面的up to half

    例:I don’t know the reason why/for which he did it.

    The reason why/for which he was fired was not clear.

    There are many countries in Asia, of which China is the largest one.

    亚洲有很多国家,中国是最大的一个。

    附:whose和 of which的区别

    1. 关系代词whose,引导定语从句时,既可指人,又可指物,在从句中只能作定语of whom只能指人;of which只能指物,有时whose可以与of whomof which互换使用。如:

    The girl whose hair is golden is from England. 头发金色的那个女孩是英国人。

    The house whose doors(of which the doors) are green is an office building. 门是绿色的那座房子是办公楼。

    2. “介词 + whose +名词”引导定语从句。如:

    I love my motherland, for whose good future I will work hard. 我爱我的祖国,为了她美好的未来我要努力工作。

    3. 在下列情况下,一般只用of whomof which

    (1) 定语从句的主语是few, little, some, most, many, much等时,一般只用of whomof which

    In the room are lots of people, many of whom I dont know. 房间里有很多人,很多人我不认识。

    He has a lot of story-books, a few of which I have never read. 他有很多故事书,有几本故事书我还从未看过。

    (2) 定语从句的主语是数词、形容词的最高级时,一般只用of whomof which如:

    The old man has three children, two of whom are college students and one of whom is a manager. 那个老人有三个小孩,其中两个是大学生,另一个是经理。

    (3) 定语从句的主语是all, none, both, neither, each等不定代词时,一般只用of whomof which如:

    There are fifty students in our class, all of whom are working hard. 我们班有五十个学生,所有这五十个学生学习都刻苦。

    He planted two trees last year, both of which are growing well. 去年他栽了两棵树,这两棵树都长得好。

    (4) 在定语从句中作表语的定语时,一般只用of whomof which如:

    He has three brothers, of whom Li Lei is the youngest one. 它有三兄弟,李雷是他们中最小的一个。

    There are many countries in Asia, of which China is the largest one. 亚洲有很多国家,中国是最大的一个。

    (5) 形式不同。如:

    The house          windows face south is our reading-room.

    A. of which    B. whose   C. which   D. its

    【解答】此题正确答案是B,不能选择A选择of which时应在名词前加上定冠词the,也就是名词前有the就只能用of which如果名词前没有冠词,就用whose。若上题改为The house          the windows face south is our reading-room,则此题只能选A不能选B了。

    (6) of which除了可以表示所属关系外,还可以用来表示整体的关系,而whose则不能。例:

    He borrowed a book of which the author was a peasant.

    In Barcelona the Chinese team got 16 gold medals, of which 12 were won by women.

    第一句中的of which就可以用whose来代替,因为这个句子中of是用来表示所属关系的。可改写成:He borrowed a book whose author was a peasant. 而第二句中的of which不能用whose代替,因为这个句子中的of不是表示所属关系,而是表示整体与部分的关系。

    (7) 引导非限制性定语从句多用whose,而很少用表示所属关系的介词of加上关系代词which如:

    There is a mysterious lake at the foot of the hill, whose depth has never been measured. 山脚下有个神秘的湖,其深度从未被测量过。

    如介词of不表示所属关系,而是用来表示整体与部分的关系,此时就经常引导非限制性定语从句了。如:

    There are 102 elements found in nature, of which most are metals.

    The story about Long March, of which this is one example, are well written.

    I’d like a car whose front lights are big and round.

    I’d like a car, the front lights of which are big and round.

    The book, the cover of which is broken, is not mine.

    The book, whose cover is broken, is not mine.

    展开全文
  • Oracle with语句的用法

    千次阅读 2011-11-30 14:44:45
    Oracle with语句是经常可以见到的语句,下面就为您详细介绍Oracle with语句的用法,如果您对Oracle with语句感兴趣的话,不妨一看。 当查询中多次用到某一部分时,可以用Oracle with语句创建一个公共临时表。因为子...

    Oracle with语句是经常可以见到的语句,下面就为您详细介绍Oracle with语句的用法,如果您对Oracle with语句感兴趣的话,不妨一看。

    当查询中多次用到某一部分时,可以用Oracle with语句创建一个公共临时表。因为子查询在内存临时表中,避免了重复解析,所以执行效率会提高不少。临时表在一次查询结束自动清除。

    一般语法格式:

    1. with   
    2. alias_name1 as    (subquery1),  
    3. alias_name2 as    (subQuery2),  
    4. ……  
    5. alias_nameN as    (subQueryN)  
    6. select col1,col2…… col3   
    7.      from alias_name1,alias_name2……,alias_nameN 

    Oracle with语句的例子:

    1. SQL> WITH  
    2. Q1 AS (SELECT 3 + 5 S FROM DUAL),  
    3.     Q2 AS (SELECT 3 * 5 M FROM DUAL),  
    4.     Q3 AS (SELECT S, M, S + M, S * M FROM Q1, Q2)  
    5. SELECT * FROM Q3; 

    输出结果:

    1. S M S+M S*M  
    2. ---------- ---------- ---------- ----------  
    3. 8 15 23 120  
    4.  

    来源:http://database.51cto.com/art/201010/231528.htm

     

    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" (Note:  You may find a faster execution plan by using Global Temporary tables, depending on your release of Oracle):

    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":

    Link: http://www.dba-oracle.com/t_with_clause.htm


    Improving Query Performance with the SQL WITH Clause

     

     

    Oracle9i significantly enhances both the functionality and performance of SQL to address the requirements of business intelligence queries. The SELECT statement's WITH clause, introduced in Oracle9i, provides powerful new syntax for enhancing query performance. It optimizes query speed by eliminating redundant processing in complex queries.

    Consider a lengthy query which has multiple references to a single subquery block. Processing subquery blocks can be costly, so recomputing a block every time it is referenced in the SELECT statement is highly inefficient. The WITH clause enables a SELECT statement to define the subquery block at the start of the query, process the block just once, label the results, and then refer to the results multiple times.

    The WITH clause, formally known as the subquery factoring clause, is part of the SQL-99 standard. The clause precedes the SELECT statement of a query and starts with the keyword "WITH." The WITH is followed by the subquery definition and a label for the result set. The query below shows a basic example of the clause:

    WITH channel_summary AS
      ( SELECT channels.channel_desc,
           SUM(amount_sold) AS channel_total
        FROM sales, channels
        WHERE sales.channel_id = channels.channel_id
        GROUP BY channels.channel_desc )
    SELECT channel_desc, channel_total
    FROM channel_summary
    WHERE channel_total >
      ( SELECT SUM(channel_total) * 1/3
        FROM channel_summary );

    This query uses the WITH clause to calculate the sum of sales for each sales channel and label the results as channel_summary. Then it checks each channel's sales total to see if any channel's sales are greater than one third of the total sales. By using the new clause, the channel_summary data is calculated just once, avoiding an extra scan through the large sales table.

    Although the primary purpose of the WITH clause is performance improvement, it also makes queries easier to read, write and maintain. Rather than duplicating a large block repeatedly through a SELECT statement, the block is localized at the very start of the query. Note that the clause can define multiple subquery blocks at the start of a SELECT statement: when several blocks are defined at the start, the query text is greatly simplified and its speed vastly improved.

    The SQL WITH clause in Oracle9i significantly improves performance for complex business intelligence queries. Together with the many other SQL enhancements in Oracle9i, the WITH clause extends Oracle's leadership in business intelligence.

    来源:http://www.eygle.com/digest/2009/04/oracle_with_clause.html

     

    展开全文
  • ORACLE WITH AS 用法

    万次阅读 2009-12-02 13:51:00
    原文传送门:http://blog.csdn.net/wh62592855/archive/2009/11/06/4776631.aspx 记得以前在论坛里看到inthirties用到过WITH AS这个字眼,当时没特别在意。今天在一个帖子里又看到有人用这个,所以就去网上搜了搜...

    原文传送门: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 a as (select * from test)

    select * from a;

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

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

    下面是搜索到的英文文档资料

    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”:

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

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

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

    展开全文
  • ORACLE WITH AS 的用法

    千次阅读 2013-09-23 16:38:17
    为了简化SQL语句,可以将语句分成若干个视图来操作,但是创建的试图将会作为对象保存在数据库中,但经常有一些语句只是临时使用,所以在sql-99规范中有了with as 语句,该语句实质上就是创建临时视图,来帮助你简化...
  • 关于oracle with table as 创建临时表的用法示例 1、with table as 相当于建个临时表(用于一个语句中某些中间结果放在临时表空间的SQL语句),Oracle 9i 新增WITH语法,可以将查询中的子查询命名,放到SELECT语句...
  • python-with open函数的用法

    万次阅读 2019-06-28 11:04:17
    Using with is also much shorter than writing equivalent try-finally blocks,同时使用with也比写 try-finally blocks更简短。 with open('workfile') as f: ... read_data = f.read() f.closed True 如果 ...
  • JUnit高级用法之@RunWith

    千次阅读 2017-10-14 22:02:00
    * @deprecated Included for backwards compatibility with JUnit 4.4. Will be * removed in the next major release. Please use * {@link BlockJUnit4ClassRunner} in place of {@link JUnit4ClassRunner}. *...
  • tensorflow中python中with用法的理解

    千次阅读 2017-09-22 19:06:23
    出处:http://blog.csdn.net/u011534057/article/details/53082106 class tf.Session ...A class for running TensorFlow operations. ...A Session object encapsulates the environment in which Operatio
  • oracle 的 start with connect by 用法

    千次阅读 2012-11-27 17:38:00
    转帖地址:... ORACLE--Connect By、Level、Start With的使用(Hierarchical query-层次查询) Syntax 1 CONNECT BY [NOCYCLE] START WITH  Sy
  • python 常用文件读写及with用法

    千次阅读 2018-07-30 13:53:00
     with用法稍后讨论,上面运行结果,那么我们深入到reader这个方法本身去看看究竟,python源代码如下: def reader(iterable, dialect='excel', *args, **kwargs): # real signature unknown; NOTE: ...
  • SQL 中 with as 用法(转)

    千次阅读 2011-12-21 09:51:44
    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 sql1ITPUB个人空间...
  • with as语法 –针对一个别名 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), ...
  • 本文翻译自Python的开发者指南PEP 492。...网上async with和async for的中文资料比较少,我把PEP 492中的官方陈述翻译一下。 异步上下文管理器”async with” 异步上下文管理器指的是在enter和exit方法处能够
  • 注意matplotlib中画散点图的scatter函数的参数c的用法 c的值不可以是列向量 import numpy as np import matplotlib.pyplot as plt if __name__ == '__main__': # 1. 数据生成 N = 100 x = np.random.uniform(low...
  • systemd用法

    千次阅读 2018-01-09 16:00:25
     files, which are in turn inspired by Microsoft Windows  .ini  files. Unit files are loaded from two locations. From lowest to highest precedence they are: /usr/lib/systemd/system/ : units ...
  • with语句也是一个功能强大的特性,但是它常常不能被正确的理解。它允许你把一个对象的所有属性放到with语句所... JavaScript中with(){}语句是如何工作的让我们首先通过一个小例子来看看with(){}语句的基本用法: var u
  • sprintf用法

    千次阅读 2018-05-21 21:28:59
    sprintf(string,"%f",num); string是一个字符串,num是你要的数字,这样就能将浮点数...Each conversion specification begins with a % and ends with a conversion character: f, e/E, g/G.   Between the % and the...
  • authconfig 用法

    千次阅读 2017-04-24 16:04:33
    linux 下authconfig命令用法
  • hcidump用法

    千次阅读 2013-09-13 11:08:01
    http://blog.csdn.net/newtonnl/article/details/8073559... hcidump reads raw HCI data coming from and going to a Bluetooth device  (which can be specified with the option -i, default is the first
  • Linux expect 用法

    万次阅读 2015-09-08 13:48:58
    ./configure --prefix=/usr/local/expect/ --with-tcl=/usr/local/tcl/lib/ --with-tclinclude=/opt/tcl8.4.11/generic/ --enable-shared make && make install 注意:指定的/opt/tcl8.4.11/generic/ 为我们上面解压...
  • Mybatis注解用法

    万次阅读 多人点赞 2019-09-10 11:21:02
    MyBatis(八) mybatis注解 一、mybatis简单注解 1、@Select、@Results、@Result 2、@Delete、@Param、@ResultMap 3、@Insert、@SelectKey 4、@Delete、@Param 5、@Update 二、动态SQL ...
  • python中的matplotlib用法

    万次阅读 多人点赞 2016-11-10 23:11:23
    这个repo 用来记录一些python技巧、书籍...使用python对matplotlib库操作使得对图形的显现极为方便,下面是用的较多的一些用法。 建议配合Ipython使用,如果通过cmd启动ipython,请使用ipython –pylab启动,方便绘...
  • nuitka用法

    千次阅读 2017-06-16 22:11:12
    Usage: nuitka [--module] [--execute] [options] main_module.py 用法:nuitka [模块] [执行] [选项] main_module.py -- Options: 选项:  --version show program's version number and exit -版本
  • EasyGui的用法简介

    千次阅读 2016-11-26 22:46:23
    用法首先要进行的就是安装easygui,大家可以在网上搜索相应的安装方法,这里就简单的介绍一个最简单的安装方法,使用安装神器pip 只需在终端或者命令行输入:pip install easygui 稍等一会大家就可以引用自己...
  • python中spidev用法

    千次阅读 2019-06-22 14:00:26
    用法(Usage): import spidev #导入spidev模块 spi=spidev.SpiDev() #面向对象编程 spi.open(bus,device) #打开spi中对应的设备 to_send=[0x01,0x02,0x03] spi.xfer(to_send) #设置串口波特率· 设置(Settings)...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 47,171
精华内容 18,868
关键字:

which的用法with