CTE在Oracle和Sqlserver中使用的差异

        CTE是一个很好用的工具，他可以帮助我们清晰代码结构，减少临时表使用，同时oracle和sqlserver都提供支持。但在oracle和sqlserver中使用CTE也存在一定区别。

• Oracle使用CTE方式

              1、CTE查询

 

          with cte as(
	     select 1 from dual 
    
             union all
	
             select 2 from dual
          )
          select * from cte;

 

              2、CTE插入

 

          insert into test
          with cte as(
	     select 1 from dual 
    
             union all
	
             select 2 from dual
          )
          select * from cte;

              3、CTE更新

 

          UPDATE  EMP_TRACKING_LIST  L
          SET ACTIVE_FLAG = 
          (
              WITH  sub_q  AS
              (
                  SELECT  ...
              )
              SELECT  ...
              FROM    sub_q  ...
          );

 

               4、CTE删除

 

          delete TCH_SCHTIME_LESSON
          where a in 
          (
              with c as (
	          Select * 
                  from b
              )
              select Tchlessonkey
              from c
              where 1 = 2
          )
          and Tchlessonkey <> 1; 

 

 

• sqlserver使用CTE方式

             1、查询方式

 

          with cte as(
	     select 1 a 
    
             union all
	
             select 2 a
          )
          select * from cte;

 

             2、插入方式

 

          with cte as(
	     select 1 a
    
             union all
	
             select 2 a
          )

          insert into test

          select * from cte;

             3、更新方式

 

 

          with cte as(
	     select 1 a
    
             union all
	
             select 2 a
          )

          update test
          set b = a
          from cte;

 

             4、删除方式

 

          with cte as(
	     select 1 a
    
             union all
	
             select 2 a
          )

          delete 
          from test,cte
          where b = a;

 

       总结：在sqlserver中，cte非常强大，比较好用可以广泛应用于select,insert,update,delete中。

                   在oracle中，cte仅仅用于查询操作，并不能灵活使用在insert，update，delete中，建议使用merge