教学文章
Technology Exchange
Technology Exchange
热门课程
400电话
免费咨询热线
400-090-9964
一、官方对with使用的解释
About Oracle WITH clause
Starting in Oracle9i release 2 we see an incorporation of the SQL-99 “WITH clause”, a tool for materializing subqueries to save Oracle from having to re-compute them multiple times.
The SQL “WITH clause” is very similar to the use of Global temporary tables (GTT), a technique that is often used to improve query speed for complex subqueries. Here are some important notes about the Oracle “WITH clause”:
• The SQL “WITH clause” only works on Oracle 9i release 2 and beyond.
• Formally, the “WITH clause” is called subquery factoring
• The SQL “WITH clause” is used when a subquery is executed multiple times
• Also useful for recursive queries (SQL-99, but not Oracle SQL)
To keep it simple, the following example only references the aggregations once, where the SQL “WITH clause” is normally used when an aggregation is referenced multiple times in a query.
We can also use the SQL-99 “WITH clause” instead of temporary tables. The Oracle SQL “WITH clause” will compute the aggregation once, give it a name, and allow us to reference it (maybe multiple times), later in the query.
The SQL-99 “WITH clause” is very confusing at first because the SQL statement does not begin with the word SELECT. Instead, we use the “WITH clause” to start our SQL query, defining the aggregations, which can then be named in the main query as if they were “real” tables:
with语法：
WITH
subquery_name
AS
(the aggregation SQL statement)
SELECT
(query naming subquery_name);
Retuning to our oversimplified example, let’s replace the temporary tables with the SQL “WITH clause”:
WITH
sum_sales AS
select
sum(quantity) all_sales from stores
number_stores AS
select
count(*) nbr_stores from stores
sales_by_store AS
select
store_name, sum(quantity) store_sales from
store natural join sales
SELECT
store_name
FROM
store,
sum_sales,
number_stores,
sales_by_store
where
store_sales > (all_sales / nbr_stores)
;
Note the use of the Oracle undocumented “materialize” hint in the “WITH clause”. The Oracle materialize hint is used to ensure that the Oracle cost-based optimizer materializes the temporary tables that are created inside the “WITH” clause. This is not necessary in Oracle10g, but it helps ensure that the tables are only created one time.
It should be noted that the “WITH clause” does not yet fully-functional within Oracle SQL and it does not yet support the use of “WITH clause” replacement for “CONNECT BY” when performing recursive queries.
To see how the “WITH clause” is used in ANSI SQL-99 syntax, here is an excerpt from Jonathan Gennick’s great work “Understanding the WITH Clause” showing the use of the SQL-99 “WITH clause” to traverse a recursive bill-of-materials hierarchy
The SQL-99 “WITH clause” is very confusing at first because the SQL statement does not begin with the word SELECT. Instead, we use the “WITH clause” to start our SQL query, defining the aggregations, which can then be named in the main query as if they were “real” tables:
with语法：
WITH
subquery_name
AS
(the aggregation SQL statement)
SELECT
(query naming subquery_name);
Retuning to our oversimplified example, let’s replace the temporary tables with the SQL “WITH” clause”:
二、案例分析
13:38:49 SCOTT@ test3 >with tt as
13:38:56 2 (select deptno,sum(sal) sum_sal,avg(sal) avg_sal,max(sal) max_sal,min(sal) min_sal from emp group by deptno)
13:39:02 3 select * from tt;
DEPTNO SUM_SAL AVG_SAL MAX_SAL MIN_SAL
---------- ---------- ---------- ---------- ----------
30 9400 1566.66667 2850 950
20 10875 2175 3000 800
10 8750 2916.66667 5000 1300
Elapsed: 00:00:00.01
13:39:07 SCOTT@ test3 >with t as
13:41:10 2 (select * from emp)
13:41:29 3 select * from t where deptno=10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
13:43:17 SCOTT@ test3 >with t1 as
13:44:55 2 (select * from emp where deptno=10),
13:44:55 3 t2 as
13:44:55 4 (select * from emp where deptno=20)
13:44:55 5 select * from t1
13:44:55 6 union all
13:44:55 7 select * from t2;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
8 rows selected.
Elapsed: 00:00:00.01
1、查询出部门的总薪水大于所有部门平均总薪水的部门。部门表s_dept，员工表s_emp。分析：做这个查询，首先必须计算出所有部门的总薪水，然后计算出总薪水的平均薪水，再筛选出部门的总薪水大于所有部门总薪水平均薪水的部门。那么第1 步with 查询查出所有部门的总薪水，第2 步用with 从第1 步获得的结果表中查询出平均薪水，最后利用这两次 的with 查询比较总薪水大于平均薪水的结果，如下：
12:49:32 SCOTT@ test3 >WITH DEPT_COSTS AS
12:50:13 2 (SELECT D.DNAME, SUM(E.SAL) DEPT_TOTAL
12:50:13 3 FROM DEPT D, EMP E
12:50:13 4 WHERE E.DEPTNO = D.DEPTNO
12:50:13 5 GROUP BY D.DNAME),
12:50:13 6 AVE_COST AS
12:50:13 7 (SELECT SUM(DEPT_TOTAL) / COUNT(*) AVG_SUM FROM DEPT_COSTS)
12:50:13 8 SELECT dname from dept_costs,ave_cost where dept_total > avg_sum;
DNAME
--------------
RESEARCH
Elapsed: 00:00:00.32
12:50:21 SCOTT@ test3 >
以下内容转自网络：
转自：http://www.linuxidc.com/Linux/2012-01/52108.htm
1） 不用实际建表，可以轻松构建一个临时表，通过对这个表的处理测试一些功能；
例如：with t as (
select '010-82696948' telfrom dualunionall
select'020 82167684'from dualunion all
select'010-62102147\62104404'from dualunion all
select'0860476-82321383'from dualunion all
select'020-28876096'from dualunion all
select'010-67260464-分机'from dual)
select '086-0'||regexp_replace(replace(regexp_substr(tel,'[0-9]+[- ][0-9]{7}',1,1),'','-'),'^[0]*86[0]|^0','')from t;
--对各种格式电话号码做规范化处理
2） 复杂的查询会产生很大的sql，with table as语法可以把一些公共查询提出来，也可以显示一个个中间结果，可以使整个sql语句显得有条理些，可读性提高；
3） 前面的中间结果可以被语句中的select或后面的中间结果表引用，类似于一个范围仅限于本语句的临时表，在需要多次查询某中间结果时可以提升效率 ，特别是对一些大数据量的表做多项统计时，可以大大提高效率。
例如：
with a as (select * from dba_objects where 某些查询条件),
b as (select * from a where 某些查询条件)
select * from b , a where 其它查询条件;
再比如：
with tb as (select * from dba_objects where 某些查询条件),
select count(*) from tb where 其它查询条件1
union
select count(*) from tb where 其它查询条件2
union
select count(*) from tb where 其它查询条件3;
1、with table as 相当于建个临时表（用于一个语句中某些中间结果放在临时表空间的SQL语句），Oracle 9i 新增WITH语法，可以将查询中的子查询命名，放到SELECT语句的最前面。
语法就是
with tempname as (select ....)
select ...
例子：
with t as
(select * from emp where depno=10)
select * from t where empno=xxx
with wd as
(select did,arg(salary) 平均工资 from work group by did),
em as (select emp.*,w.salary from emp left join work w on emp.eid = w.eid)
select * from wd,em where wd.did =em.did and wd.平均工资>em.salary;
2、何时被清除
临时表不都是会话结束就自动被PGA清除嘛! 但with as临时表是查询完成后就被清除了！
23:48:58 SCOTT@orcl> with aa as(select * from dept)
23:57:58 2 select * from aa;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
已用时间: 00: 00: 00.12
23:58:06 SCOTT@orcl> select * from aa;
select * from aa
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
3、举例
假定有张很大的表，有几年来的经营数据，数据量很大。如果要统计一段时间内的邮件状态，如果都从总表中统计，效率一定不高，而采用with tablename as 语句，先将一段时间内的数据取出来，再进行统计就会简单的多。
with tb as (
select b.city,a.mail_num,a.rcv_area from tb_evt_mail_clct a, tb_jg b
where a.clct_date = to_date('20110816', 'yyyymmdd')
and (a.rcv_area like '23%' or a.rcv_area like '24%')
and a.clct_bureau_org_code = b.zj_code
and not exists (select 1 from tb_evt_dlv c
where c.mail_num = a.mail_num
and c.dlv_sts_code = 'I')
) -- 提取出查询数据
select aa.city 收寄城市, aa.wtt 未妥投, bb.wtd 未投递, cc.wkc 未开拆
from (select tb.city, count(*) wtt
from tb
group by tb.city) aa -- 统计1
left join (select tb.city, count(*) wtd
from tb
where not exists
(select 1 from tb_evt_dlv c
where c.mail_num = tb.mail_num
and (c.dlv_sts_code = 'H' or c.dlv_sts_code = 'I'))
group by tb.city) bb on bb.city = aa.city -- 统计2
left join (select tb.city, count(*) wkc
from tb
where not exists
(select 1 from tb_evt_dlv c
where c.mail_num = tb.mail_num
and (c.dlv_sts_code = 'H' or c.dlv_sts_code = 'I'))
and not exists
(select 1 from tb_evt_bag_mail_rela e
where e.mail_num = tb.mail_num
and e.bag_actn_code = '2'
and e.deal_org_code like
substr(tb.rcv_area, 1, 4) || '%')
group by tb.city) cc on cc.city = aa.city -- 统计3
转自：http://my.oschina.net/GeminiLiu/blog/191651
经常在开发过程中会用到视图或组合查询的情况，但由于涉及表数据经常达到千万级别的笛卡尔积，而且一段查询时会反复调用，但结果输出往往不需要那么多，可以使用with将过滤或处理后的结果先缓存到临时表（此处原理不太清楚，仅代表我的理解），可以大大提高查询效率
/*
查询当前EOMS流程组及组织架构信息
create by Gemini.Liu
2014-01-07
*/
--create or replace view v_getcurrent_workflow as
--组信息
with group_info as
(
select gp1.group_intid,gp1.group_id,gp1.group_name,gp1.group_fullname,
decode(gp1.group_type,3,gp1.group_fullname,2,gp1.group_fullname,gp2.group_fullname) arch,
decode(gp1.group_type,3,gp1.group_id,2,gp1.group_id,gp2.group_id) archid
from ultraprocess_sysgroup gp1,ultraprocess_sysgroup gp2
where 1=1
and gp1.group_parentid=gp2.group_id
and (gp2.group_type = 3 or gp2.group_type = 2)
),
--组成员
group_user as
(
select WMSYS.WM_CONCAT(us1.user_fullname) person,WMSYS.WM_CONCAT(us1.user_loginname) personid,us2.group_intid from ultraprocess_sysuser us1,ultraprocess_sysgroup us2,ultraprocess_sysgroupuser us3
where 1=1
and us1.user_id=us3.mgroup_userid
and us2.group_id=us3.mgroup_groupid
group by us2.group_intid
),
--映射信息
dp_map as
(
select hj.groupid,hj.processbaseschema from wf_app_dealprocess hj
where 1=1
and hj.groupid is not null
and hj.processbaseschema is not null
and hj.edprocessaction<> 19
group by groupid,processbaseschema
)
/*
--工单信息
,form_info as
(
select info.basesn,info.baseschema,info.basename,info.basestatus,info.baseid,hj.group_x,hj.groupid,hj.flagactive from wf_app_base_infor info,wf_app_dealprocess hj
where 1=1
and info.baseid=hj.processbaseid
and info.baseschema = hj.processbaseschema
and info.basestatus <> '已作废'
and hj.edprocessaction<> 19
)
*/
--展现信息
select dp_map.processbaseschema 工单类别,
group_info.group_intid 组ID,
group_info.group_name 组名,
group_user.person 组成员,
group_user.personid 组成员登录名,
group_info.arch 所属部门,
group_info.archid 所属部门ID
from dp_map,group_info,group_user
where 1=1
and dp_map.groupid = group_info.group_intid
and group_user.group_intid = group_info.group_intid
转自：http://www.cnblogs.com/linjiqin/archive/2013/06/24/3152667.html
--相当于建了个e临时表
with e as (select * from scott.emp e where e.empno=7499)
select * from e;
--相当于建了e、d临时表
with
e as (select * from scott.emp),
d as (select * from scott.dept)
select * from e, d where e.deptno = d.deptno;
其实就是把一大堆重复用到的sql语句放在with as里面，取一个别名，后面的查询就可以用它，这样对于大批量的sql语句起到一个优化的作用，而且清楚明了。
向一张表插入数据的with as用法
insert into table2
with
s1 as (select rownum c1 from dual connect by rownum <= 10),
s2 as (select rownum c2 from dual connect by rownum <= 10)
select a.c1, b.c2 from s1 a, s2 b where...;
select s1.sid, s2.sid from s1 ,s2需要有关联条件，不然结果会是笛卡尔积。
with as 相当于虚拟视图。
with as短语，也叫做子查询部分(subquery factoring)，可以让你做很多事情，定义一个sql片断，该sql片断会被整个sql语句所用到。有的时候，是为了让sql语句的可读性更高些，也有可能是在union all的不同部分，作为提供数据的部分。
特别对于union all比较有用。因为union all的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用with as短语，则只要执行一遍即可。如果with as短语所定义的表名被调用两次以上，则优化器会自动将with as短语所获取的数据放入一个temp表里，如果只是被调用一次，则不会。而提示materialize则是强制将with as短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。
with
sql1 as (select to_char(a) s_name from test_tempa),
sql2 as (select to_char(b) s_name from test_tempb where not exists (select s_name from sql1 where rownum=1))
select * from sql1
union all
select * from sql2
union all
select 'no records' from dual
where not exists (select s_name from sql1 where rownum=1)
and not exists (select s_name from sql2 where rownum=1);
with as优点
增加了sql的易读性，如果构造了多个子查询，结构会更清晰；
更重要的是：“一次分析，多次使用”，这也是为什么会提供性能的地方，达到了“少读”的目标