总体来说相当于创建临时表
with table1 as (
select * from a
)
select * from table1 --从上面的结果集中查询
可以创建多个临时表
with
table1 as (
select * from a
),
table2 as (
select * from b
)
select * from table1,table2 where table1.id=table2.id
如果临时表的名字和真实的表名重复了,第一个查询是临时表,后面的是真实表
with table1 as(
select * from a
)
select * from table1 --临时表
select * from table1 --真实表
不能在with中定义下列语句:
(1)COMPUTE 或 COMPUTE BY
(2)ORDER BY(除非指定了 TOP 子句)
(3)INTO
(4)带有查询提示的 OPTION 子句
(5)FOR XML
(6)FOR BROWSE
在同级只能有一个with,不同级可以有多个
SELECT a ,b
FROM (
--第1个定义t_with
WITH
t_with AS (SELECT 1 a FROM DUAL)
--子查询使用t_with
SELECT x.a ,(
--内部定义了个t_with_z,并且使用t_with
WITH t_with_z as (SELECT 1 a FROM t_with )
SELECT s_1.a FROM t_with_z s_1 ,t_with s_2
一个with查询的实例:
查询出部门的总薪水大于所有部门平均总薪水的部门。部门表s_dept,员工表s_emp。分析:做这个查询,首先必须计算出所有部门的总薪水,然后计算出总薪水的平均薪水,再筛选出部门的总薪水大于所有部门总薪水平均薪水的部门。那么第1 步with查询查出所有部门的总薪水,第2 步用with 从第1 步获得的结果表中查询出平均薪水,最后利用这两次的with查询比较总薪水大于平均薪水的结果,如下:
with
--step1:查询出部门名和部门的总薪水
dept_costs as(
select a.name,sum(b.salary) dept_total
from
s_dept a,s_emp b
where a.id=b.dept_id
group by a.name
),
--step2:利用上一个with查询的结果,计算部门的平均总薪水
avg_costs as(
select sum(dept_total)/count(*) dept_avg
from dept_costs
)
--step3:从两个with查询中比较并且输出查询结果
select name,dept_total
from dept_costs
where
dept_total>
(
select dept_avg
from
avg_costs
)
order by name;
从上面的查询可以看出,前面的with 查询的结果可以被后面的with查询重用,并且对with查询的结果列支持别名的使用,在最终查询中必须要引用所有with 查询,否则会报错ora-32035 错误。