关于oracle with as用法

素颜马尾好姑娘i 2022-08-06 05:08 150阅读 0赞

**[with as][]语法**  
–针对一个别名  
with tmp as (select \* from tb\_name)

–针对多个别名  
with  
tmp as (select \* from tb\_name),  
tmp2 as (select \* from tb\_name2),  
tmp3 as (select \* from tb\_name3),  
…

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div> 
    <div>
      9 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>--相当于建了个e临时表</code> 
     </div> 
     <div> 
      <code>with</code> 
      <code> e </code> 
      <code>as</code> 
      <code>(</code> 
      <code>select</code> 
      <code>* </code> 
      <code>from</code> 
      <code>scott.emp e </code> 
      <code>where</code> 
      <code>e.empno=7499)</code> 
     </div> 
     <div> 
      <code>select</code> 
      <code> * </code> 
      <code>from</code> 
      <code>e;</code> 
     </div> 
     <div></div> 
     <div> 
      <code>--相当于建了e、d临时表</code> 
     </div> 
     <div> 
      <code>with</code> 
     </div> 
     <div> 
      <code></code> 
      <code>e</code> 
      <code>as</code> 
      <code>(</code> 
      <code>select</code> 
      <code>* </code> 
      <code>from</code> 
      <code>scott.emp),</code> 
     </div> 
     <div> 
      <code></code> 
      <code>d</code> 
      <code>as</code> 
      <code>(</code> 
      <code>select</code> 
      <code>* </code> 
      <code>from</code> 
      <code>scott.dept)</code> 
     </div> 
     <div> 
      <code>select</code> 
      <code> * </code> 
      <code>from</code> 
      <code>e, d </code> 
      <code>where</code> 
      <code>e.deptno = d.deptno;</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

其实就是把一大堆重复用到的sql语句放在with as里面，取一个别名，后面的查询就可以用它，这样对于大批量的sql语句起到一个优化的作用，而且清楚明了。

向一张表插入数据的[with as用法][with as]

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>insert</code> 
      <code> into</code> 
      <code>table2</code> 
     </div> 
     <div> 
      <code>with</code> 
     </div> 
     <div> 
      <code></code> 
      <code>s1</code> 
      <code>as</code> 
      <code>(</code> 
      <code>select</code> 
      <code>rownum c1 </code> 
      <code>from</code> 
      <code>dual </code> 
      <code>connect</code> 
      <code>by</code> 
      <code>rownum &lt;= 10),</code> 
     </div> 
     <div> 
      <code></code> 
      <code>s2</code> 
      <code>as</code> 
      <code>(</code> 
      <code>select</code> 
      <code>rownum c2 </code> 
      <code>from</code> 
      <code>dual </code> 
      <code>connect</code> 
      <code>by</code> 
      <code>rownum &lt;= 10)</code> 
     </div> 
     <div> 
      <code>select</code> 
      <code> a.c1, b.c2 </code> 
      <code>from</code> 
      <code>s1 a, s2 b</code> 
      <code>where</code> 
      <code>...;</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

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短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div> 
    <div>
      9 
    </div> 
    <div>
      10 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>with</code> 
     </div> 
     <div> 
      <code></code> 
      <code>sql1</code> 
      <code>as</code> 
      <code>(</code> 
      <code>select</code> 
      <code>to_char(a) s_name </code> 
      <code>from</code> 
      <code>test_tempa),</code> 
     </div> 
     <div> 
      <code></code> 
      <code>sql2</code> 
      <code>as</code> 
      <code>(</code> 
      <code>select</code> 
      <code>to_char(b) s_name </code> 
      <code>from</code> 
      <code>test_tempb </code> 
      <code>where</code> 
      <code>not</code> 
      <code> exists (</code> 
      <code>select</code> 
      <code>s_name </code> 
      <code>from</code> 
      <code>sql1 </code> 
      <code>where</code> 
      <code>rownum=1))</code> 
     </div> 
     <div> 
      <code>select</code> 
      <code> * </code> 
      <code>from</code> 
      <code>sql1</code> 
     </div> 
     <div> 
      <code>union</code> 
      <code> all</code> 
     </div> 
     <div> 
      <code>select</code> 
      <code> * </code> 
      <code>from</code> 
      <code>sql2</code> 
     </div> 
     <div> 
      <code>union</code> 
      <code> all</code> 
     </div> 
     <div> 
      <code>select</code> 
      <code> 'no records'</code> 
      <code>from</code> 
      <code> dual</code> 
     </div> 
     <div> 
      <code></code> 
      <code>where</code> 
      <code>not</code> 
      <code>exists (</code> 
      <code>select</code> 
      <code>s_name </code> 
      <code>from</code> 
      <code>sql1 </code> 
      <code>where</code> 
      <code>rownum=1)</code> 
     </div> 
     <div> 
      <code></code> 
      <code>and</code> 
      <code>not</code> 
      <code>exists (</code> 
      <code>select</code> 
      <code>s_name </code> 
      <code>from</code> 
      <code>sql2 </code> 
      <code>where</code> 
      <code>rownum=1);</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

**with as优点**  
增加了sql的易读性，如果构造了多个子查询，结构会更清晰；  
更重要的是：“一次分析，多次使用”，这也是为什么会提供性能的地方，达到了“少读”的目标

[with as]: http://www.tieguanyin168.com/index.php/with-as-1191.html