以例子学习with:
with --查询部门和部门的总薪水 dept_costs as ( select d.department_name,sum(e.salary) dept_total from departments d,employees e where d.department_id=e.department_id group by d.department_name ), --利用上一个with查询的结果,计算部门的平均总薪水 avg_costs as ( select avg(dept_total) dept_avg from dept_costs ) --从两个with查询中比较并且输出查询结果 select * from dept_costs where dept_total > (select dept_avg from avg_costs) 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的可读性较强
案例:
Ⅰ一般使用方式
with --查询销售部门员工的姓名 saler_name as ( select department_id from departments where department_name='SALES' order by department_id ) select last_name,first_name from employees e where department_id in (select * from saler_name)
注释:使用with子句,可以在复杂的查询中预先定义好一个结果集,然后在查询中反复使用,不使用会报错。而且with子句获得的是一个临时表,必须采用select from (with查询名)
Ⅱ 在多数子查询中引用,同级可见
select last_name from (with --查询销售部门员工的姓名 saler_name as ( select department_id from departments where department_name='SALES' order by department_id ) select last_name,first_name from employees e where department_id in (select * from saler_name) )
Ⅲ 在集合在引用
集操作的两个select调用被当做是同级的,不能出现两个with定义
with --查询销售部门员工的姓名 saler_name as ( select department_id from departments where department_name='SALES' order by department_id ) select last_name,first_name from employees e where department_id in (select * from saler_name) union all select last_name,to_char(null) from employees