CTE - Common Table Expression(公用表达式)是SQL 2005最重要的改进之一。
子查询有时候使用起来嵌套很复杂， 而使用#tmp类似的临时表， 性能又比较差。
这个时候，介于两者之间的解决方案，CTE诞生了。
我们可以用它来替代临时表
在使用CTE时应注意如下几点：
1. CTE后面必须直接跟使用CTE的SQL语句（如select、insert、update等），否则，CTE将失效
2. CTE后面也可以跟其他的CTE，但只能使用一个with，多个CTE中间用逗号（,）分隔
如下面的SQL语句所示：
with
cte1 as
(
select * from table1 where name like 'abc%'
),
cte2 as
(
select * from table2 where id > 20
),
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后面的SQL语句使用的仍然是CTE，当然，后面的SQL语句使用的就是数据表或视图了
如下面的SQL语句所示：
-- table1是一个实际存在的表
with
table1 as
(
select * from persons where age < 30
)
select * from table1 -- 使用了名为table1的公共表表达式
select * from table1 -- 使用了名为table1的数据表
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 用在属于批处理的一部分的语句中，那么在它之前的语句必须以分号结尾
如下面的SQL所示：
declare @s nvarchar(3)
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)
CTE除了可以简化嵌套SQL语句外，还可以进行递归调用
闲话不多说， 来看看我亲自创建的一个查询的例子， 以AdventureWorks数据库为例
with BlueProducts(ProductID, Name, ProductNumber, Color)
as
(
select ProductID, Name, ProductNumber, Color
from Production.Product
where Color='Blue'
),
PriceOrderDetail(UnitPriceSum, ProductID)
as
(
select SUM(UnitPrice), ProductID
from Sales.SalesOrderDetail
group by ProductID
having SUM(UnitPrice)>100000
)
select sod.ProductID, sod.SalesOrderID, BlueProducts.Name as ProductName,
BlueProducts.ProductNumber, BlueProducts.Color, sod.UnitPrice, po.UnitPriceSum
from Sales.SalesOrderDetail as sod inner join BlueProducts
on sod.ProductID=BlueProducts.ProductID inner join PriceOrderDetail po
on sod.ProductID=po.ProductID
order by po.UnitPriceSum
892 51084 HL Touring Frame - Blue, 54 FR-T98U-54 Blue 602.346 101726.2003
892 51106 HL Touring Frame - Blue, 54 FR-T98U-54 Blue 602.346 101726.2003
......
959 71918 Touring-3000 Blue, 58 BK-T18U-58 Blue 445.41 118367.7075
959 71894 Touring-3000 Blue, 58 BK-T18U-58 Blue 445.41 118367.7075
......
966 51126 Touring-1000 Blue, 46 BK-T79U-46 Blue 1382.7606 800403.8211
966 51131 Touring-1000 Blue, 46 BK-T79U-46 Blue 1311.2385 800403.8211