高级sql学习——with子句

使用WITH AS 语句可以为一个子查询语句块定义一个名称，使用这个子查询名称可以在查询语句的很多地方引用这个子查询。Oracle 数据库像对待内联视图或临时表一样对待被引用的子查询名称，从而起到一定的优化作用。with子句是9i新增语法。你可以在任何一个顶层的SELECT 语句以及几乎所有类型的子查询语句前，使用子查询定义子句。被定义的子查询名称可以在主查询语句以及所有的子查询语句中引用，但未定义前不能引用。 with子句中不能嵌套定义<也就是with子句中不能有with子句>，但子查询中出现的“子查询定义”语句可以引用已定义的子查询名称。<可以引用前面已经定义的with子句>

 

with子句优点:
1. SQL可读性增强。比如对于特定with子查询取个有意义的名字等。

2. with子查询只执行一次，将结果存储在用户临时表空间中，可以引用多次，增强性能。

 

with子句语法：

With alias_name as (select1), --as和select中的括号都不能省略
alias_name2 as (select2),--后面的没有with，逗号分割，同一个主查询同级别地方，with子查询只能定义一次
…
alias_namen as (select n) –与下面的实际查询之间没有逗号
Select ….

 

with子句相关总结：

1.使用with子句可以让子查询重用相同的with查询块,通过select调用（with子句只能被select查询块引用），一般在with查询用到多次情况下。在引用的select语句之前定义,同级只能定义with关键字只能使用一次,多个用逗号分割。
2.with子句的返回结果存到用户的临时表空间中，只做一次查询，反复使用,提高效率。
3.在同级select前有多个查询定义的时候，第1个用with，后面的不用with，并且用逗号隔开。
5.最后一个with 子句与下面的查询之间不能有逗号，只通过右括号分割,with 子句的查询必须用括号括起来
6. 如果定义了with子句，而在查询中不使用，那么会报ora-32035 错误：未引用在with子句中定义的查询名。（至少一个with查询的name未被引用，解决方法是移除未被引用的with查询），注意：只要后面有引用 的就可以，不一定非要在主查询中引用，比如后面的with
查询也引用了，也是可以的。
7.前面的with子句定义的查询在后面的with子句中可以使用。但是一个with子句内部不能嵌套with子句。
8.当一个查询块名字和一个表名或其他的对象相同时，解析器从内向外搜索，优先使用子查询块名字。
9.with查询的结果列有别名，引用的时候必须使用别名或*。

 

with使用例子:

1.一般使用方式

with num as (select d.deptno from dept d where d.deptno=10)
select e.ename,e.job,e.sal from emp e where e.deptno in (select * from num);

ENAME      JOB             SAL
---------- --------- ---------
MILLER     CLERK       1300.00
KING       PRESIDENT   5000.00
CLARK      MANAGER     2450.00

 

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

 

with num as (select count(*) from emp e where e.deptno=10)
select num*10 from dual;
--是错误的。必须是
with num as (select count(*) c from emp e where e.deptno=10)
select c*10 from num;

      C*10
----------
        30

 

2.子查询中可以引用前面已经定义的with子句

select e.ename from emp e where e.deptno in
(select d.deptno from dept d where d.deptno=10);

with a as (select e.ename,e.deptno from emp e)
select a.ename from a where a.deptno in(
  with b as (select d.deptno from dept d where d.deptno=10)
  select * from b
);

with a as (select e.ename,e.deptno from emp e),
     b as (select d.deptno from dept d where d.deptno=10)
select a.ename from a where a.deptno in (select * from b);

ENAME
----------
MILLER
KING
CLARK

 

3.一个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 错误。

 

4.一个查询，如果查询的结果行不满足是10 的倍数，则补空行，直到是查询出的行数是10 的倍数。

with a as (select 10-mod(count(*),10) from dept d) --查询比10的倍数差几个空行
select t.dname,t.loc from dept t
union all
select null,null from all_objects --9i只能这样写
where rownum <= (select * from a);

with a as (select 10-mod(count(*),10) from dept d) --查询比10的倍数差几个空行
select t.dname,t.loc from dept t
union all
select null,null from dual --10g可以这样写
connect by rownum <= (select * from a);

官方解释：

The WITH Clause Usage Notes
• It is used only with SELECT statements.
• A query name is visible to all WITH element query blocks (including their subquery blocks)
defined after it and the main query block itself (including its subquery blocks).
• When the query name is the same as an existing table name, the parser searches from the inside
out, the query block name takes precedence over the table name.
• The WITH clause can hold more than one query. Each query is then separated by a comma.