当前位置:  数据库>oracle

关于Oracle with table as的用法

    来源: 互联网  发布时间:2017-04-20

    本文导语: 最近在论坛经常看到有人使用with table as语句,一般都是构建一个临时表,用于测试,经研究此语句的用法我理解有以下好处: 1)  不用实际建表,可以轻松构建一个临时表,通过对这个表的处理测试一些功能; 例如:with t as ...

最近在论坛经常看到有人使用with table asobjects iis7站长之家,一般都是构建一个临时表,用于测试,经研究此语句的用法我理解有以下好处:

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

例如:with t as (

select  telfrom dualunionall

selectfrom dualunion all

selectfrom dualunion all

selectfrom dualunion all

selectfrom dualunion all

selectfrom dual)

select ||regexp_replace(replace(regexp_substr(tel,,1,1),,),,)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;

 

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


    
 
 

您可能感兴趣的文章:

  • oracle中lpad函数的用法详解
  • Oracle中decode函数的用法
  • Oracle 系统变量函数用法指南
  • oracle while的用法示例分享
  • c#中oracle to_date函数用法举例
  • oracle 下WITH CHECK OPTION用法
  • Oracle 函数用法之decode
  • Oracle sequence序列的用法
  • [Oracle] 常用工具集之SQL*Loader的用法
  • oracle case when 语句的用法详解
  • Oracle行级锁的特殊用法简析
  • Oracle merge into用法及例子
  • oracle comment命令用法示例分享
  • Oracle to_date()函数的用法介绍
  • oracle正则表达式regexp_like的用法详解
  • Oracle to_date() 用法细节
  • 深入探讨:oracle中row_number() over()分析函数用法
  • Oracle中start with...connect by prior子句用法
  • Oracle回滚段的概念,用法和规划及问题的解决
  • Oracle MERGE INTO的用法示例介绍
  • oracle drop table(表)数据恢复方法
  • Oracle Table Demo语句应用介绍
  • 使用Oracle Partition Table对日志表进行分区
  •  
    本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
    本站(WWW.)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。












  • 相关文章推荐
  • Oracle 12c发布简单介绍及官方下载地址
  • 在linux下安装oracle,如何设置让oracle自动启动!也就是让oracle那个服务自动启动,不是手动的
  • oracle 11g最新版官方下载地址
  • 请问su oracle 和su - oracle有什么不同?
  • Oracle 数据库(oracle Database)Select 多表关联查询方式
  • 虚拟机装Oracle R12与Oracle10g
  • Oracle数据库(Oracle Database)体系结构及基本组成介绍
  • Oracle 数据库开发工具 Oracle SQL Developer
  • 如何设置让Oracle SQL Developer显示的时间包含时分秒
  • Oracle EBS R12 支持 Oracle Database 11g
  • Oracle 10g和Oracle 11g网格技术介绍
  • SCO unix下安装oracle,但没有光盘,请大家推荐一个oracle下载站点(unix版本的)。谢谢!!!!
  • oracle中如何把表中具有相同值列的多行数据合并成一行
  • 请问大家用oracle数据库, 用import oracle.*;下的东西么? 还是用标准库?
  • Oracle 数据库(oracle Database)性能调优技术详解
  • Linux /$ORACLE_HOME $ORACLE_HOME
  • ORACLE日期相关操作
  • Linux系统下Oracle的启动与Oracle监听的启动
  • ORACLE数据库常用字段数据类型介绍
  • 请问在solaris下安装ORACLE,用root用户和用oracle用户安装有什么区别么?
  • Oracle 12c的九大最新技术特性介绍
  • 网间Oracle的连接,远程连接Oracle服务器??


  • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

    ©2012-2021,,E-mail:www_#163.com(请将#改为@)

    浙ICP备11055608号-3