SQL中with cte as用法

转自http://blog.knowsky.com/221895.htm

一、with as 含义

with as 短语，也叫做子查询部分（subquery factoring），主要是定义一个SQL片段，该SQL片段会被整个SQL语句所用到，也有可能在union all的不同部分，作为提供数据的部分。特别对于UNION ALL比较有用，因为union all的每一个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以使用with as短语，则只要执行一遍即可。如果with as短语所定义的表名被调用两次以上，则优化器会自动将with as 短语所获取的数据放入一个temp表里。

二、使用方法

• 例子：select * from person.StateProvince where CountryRegionCode in (select CountryRegionCode from person.CountryRegion where Name like 'C%')

上述例子是嵌套查询语句，这样的SQL语句即难阅读又难维护。因此可以使用表变量的方式来解决这个问题，如下

declare @table(CountryRegionCode varchar(100))

insert into @table(CountryRegionCode)(select CountryRegionCode from Person.CoutryRegion where Name like 'C%')

select * from Person.StateProvience where CountryRegionCode in (select * from @table)

虽然这种SQL 语句比第一种方式更复杂，但却将子查询放在了表变量@table中，这样将使SQL语句更容易维护，但又会带来性能的损失。由于表变量实际上使用了临时表，从而增加了额外的O/I开销。因此，表变量的方式并不太适合数据量大且频繁查询的情况。因此，另一种解决方案，就是公用表表达式common table express(CTE)，可以使SQL语句的可维护性，同时CTE要比表变量的效率高很多。

• CTE（common table express）语法

WITH Common_table_express [(column_name[,n])]AS (CTE_query_definitation)

用CTE解决上述问题为：

with cte(CountryRegionCode) as

(

　　select CountryRegionCode from person.CoutryRegionCode where Name like 'C%'

)

select * from person.StateProvince where CountryRegionCode in (select * from cte)

其中cte是公用表表达式，该表达式在使用上与表变量类似，只是SQL 在处理方式上不同。

• 使用CTE注意事项

1、CTE后面必须直接跟使用CTE的SQL语句（如select、insert、update等），否则CTE将失效，如下面的例子将无法正确使用CTE

with cr as (select CountryRegionCode from person.CountryRegion where Name like'C%')

Select * from person.CountryRegion

select * from person.StateProvince where CountryRegionCode in (select * from cr)

with cr as 与最后面使用cr的语句之间不应该有其他语句，应该去掉‘’Select * from person.CountryRegion’后面使用的cr才有效。

2、CTE后面可以跟其他的CTE，但只能使用一个with，多个CTE之间用逗号（，）分隔，例如：

with cte1 as (select * from table1 where name like 'abc%'),

cte2 as (select * from table2 where id>2),

cte3 as (select * from table3 where price<100)

select a.* from cte1 a,cte2 b,cte3 c where a.id=b.id and a.id=c.id

3、如果CTE的名称与实际表名或者视图名称相同，那么紧随cte后面的针对cte名称的操作是针对CTE的，而再紧接的cte名称操作则是针对实际表名或视图的。

4、CTE可以引用自身，也可以引用在同一with子句中预先定义的cte。不允许向前引用。

5、不允许在CTE_Query_Definition中使用以下子句：

　　1）COMPUTE 或COMPUTE BY(计算或分组计算)

　　2）ORDER BY(除非指定了TOP子句)

　　3）INTO

　　4）带有查询提示的OPTION子句

　　5）FOR XML

　　6）FOR BROWSE

6、如果是将CTE用在属于批处理的一部分的语句中，那么在它之前的语句必须以分号结尾，如下：

　　declare @s varcher(100) set @s='C%'

　　;

　　WITH t_tree as 

　　(select CountryRegionCode from person.CountryRegion where Name like @S)

　　select * from person.StateProvince where CountryRegionCode in (select * from t_tree)

7、with cte as()不能嵌套使用