以例子学习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
分享到:
2012-09-18 10:17
浏览 370
评论