Oracle Study之---Oracle SQL语句中with的使用

女爷i 2022-08-04 12:00 1099阅读 0赞

**Oracle Study之---Oracle SQL语句中with的**

**一、官方对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的易读性，如果构造了多个子查询，结构会更清晰；  
更重要的是：“一次分析，多次使用”，这也是为什么会提供性能的地方，达到了“少读”的目标