mssql sqlserver “with as”关键字用法举例说明


摘要:
下文举例讲解with as cte公用表达式的用法,如下所示:
实验环境:sql server 2008 R2


当一个结果集,在一条sql脚本中,需多次调用执行时,此时我们可以将这个结果集所涉及的查询脚本,使用with as 组合。
例:

 with  newTableName as (select a,b,c from tableName t1 left join ... t2 on t1..=t2.. left join ..t3 ...);
  select * from newTableName a left join  newTableName b on ...;
---同时定义多个cte公用表达式,需采用都好分隔
---例:
 with  newTableName as (select a,b,c from tableName t1 left join ... t2 on t1..=t2.. left join ..t3 ...)
          ,newTableName2 as (select a,b,c from tableName t1 left join ... t2 on t1..=t2.. left join ..t3 ...)
         ,newTableName3 as (select a,b,c from tableName t1 left join ... t2 on t1..=t2.. left join ..t3 ...)
         ;
  select * from newTableName a left join  newTableName2 b on ... left join newTableName3 c on ...;
 


with as 使用注意事项及优点:
with as 定义的脚本的前面需使用逗号分隔,定义完毕后也需使用逗号结尾,之后的第一条sql脚本需使用with as 所定义的对象,
第二条sql脚本中 with as 定义失效。
with as 的优点:
使代码更加优雅、更加简单、更加清晰
性能高,因为涉及多次调用,数据被一次缓存至数据库引擎中
占用空间少,with as 使用完毕后,立即被清除,消耗的内存空间更小
with as 代码中不能包括以下定义:
order by (除非指定top)、into、for xml、for browse、option子句、compute
with as 不能嵌套定义,如:with as ( with as (…));