ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle with子句 (转)

oracle with子句 (转)

原创 Linux操作系统 作者:流浪的野狼 时间:2013-10-07 11:54:19 0 删除 编辑
以例子学习with:
  1. with  
  2. --查询部门和部门的总薪水   
  3.   dept_costs as (  
  4.                  select d.department_name,sum(e.salary) dept_total  
  5.                    from departments d,employees e  
  6.                   where d.department_id=e.department_id  
  7.                   group by d.department_name  
  8.                  ),  
  9. --利用上一个with查询的结果,计算部门的平均总薪水   
  10.   avg_costs as (  
  11.                 select avg(dept_total) dept_avg  
  12.                   from dept_costs  
  13.                 )  
  14. --从两个with查询中比较并且输出查询结果   
  15.   select *  
  16.     from dept_costs  
  17.    where dept_total > (select dept_avg from avg_costs)  
  18.   order by department_name  


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

 

    再来看with的语法

          ㈠ as和select中的括号不能省略
          ㈡ 同级别select调用,with只能定义一次,多个用逗号分隔,但最后一个with子查询与下面的实际查询之间没有逗号

 

    with子句的优点

           ① with子句有可能会改变执行计划
           ② with子查询只执行一次,将结果存储在用户的临时表空间,可多次引用,增加性能
           ③ sql的可读性较强

 

    案例:

    Ⅰ一般使用方式

  1. with  
  2. --查询销售部门员工的姓名   
  3.   saler_name as (  
  4.                  select department_id from departments  where department_name='SALES' order by department_id  
  5.                  )  
  6. select last_name,first_name  
  7.   from employees e  
  8.  where department_id in (select * from saler_name)  


    注释:使用with子句,可以在复杂的查询中预先定义好一个结果集,然后在查询中反复使用,不使用会报错。而且with子句获得的是一个临时表,必须采用select from (with查询名)

 

    Ⅱ 在多数子查询中引用,同级可见

  1. select last_name  
  2.   from (with  
  3. --查询销售部门员工的姓名   
  4.   saler_name as (  
  5.                  select department_id from departments  where department_name='SALES' order by department_id  
  6.                  )  
  7. select last_name,first_name  
  8.   from employees e  
  9.  where department_id in (select * from saler_name)  
  10.         )  


 

    Ⅲ 在集合在引用

    集操作的两个select调用被当做是同级的,不能出现两个with定义

  1. with  
  2. --查询销售部门员工的姓名   
  3.   saler_name as (  
  4.                  select department_id from departments  where department_name='SALES' order by department_id  
  5.                  )  
  6. select last_name,first_name  
  7.   from employees e  
  8.  where department_id in (select * from saler_name)  
  9. union all  
  10. select last_name,to_char(null)  
  11.   from employees  

from:http://blog.csdn.net/dba_waterbin/article/details/7990594

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28612416/viewspace-773797/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 关于时区的划分
下一篇: oracle的监听问题
请登录后发表评论 登录
全部评论

注册时间:2013-04-10

  • 博文量
    151
  • 访问量
    1510084