SQL Server中With AS语句的用法及注意事项

时　间:2013-02-22 08:34:24
作　者:缪炜   ID：24010  城市：江阴
摘　要:居然今天才发现有这么好一个语句：with as，实在惭愧。
正　文:

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 as语法：with 数据表别名 as (select * from ...)

实际应用实例：

with t as (select * from emp where depno=10)

select * from t where empno=xxx

union all sum(col1) as colsum from t

看到了吧？可以很方便的对这个临时表t进行过滤、汇总等操作。而且这种方法比其它的sql语句效率要高很多！

需要注意的是，with as（CTE）属于sql server2005及以上版本才拥有的功能，如果在sql server 2000上执行，会提示错误“WITH附近有语法错误”