SQL Server 公用表表达式CTE

简介

为了使T-SQL代码更加简洁和可读，在一个查询中引用的结果集都是引用视图而不是子查询来进行分解的，视图是作为系统对象存储在数据库中的，对于结果集只需要使用一次的时候，就显得有点奢侈了。

公用表表达式（Command Table Epression）是SQL Server 2005引进的一个特性，CTE可以看作是一个临时的结果集，可以在 接下来的SELECT,UPDATE,INSERT,DELETE语句多次使用

好处：

• 可以使用递归的CTE
• 增强代码的可读性，简洁性

CTE的定义

1.公用表表达式的名称 

2.所涉及列的名称

3.一个SELECT语句，跟在AS后面

 

;WITH cte_name [（col_name,..）]
AS(
    SELECT ..
)

如果CTE不是批处理的第一句语句，则必须在WITH前面加多个分号；，作为最佳的做法，无论是第一句还是其他位置，都加上分号为标准。按照是否递归，可以分为递归CTE和非递归CTE

非递归CTE

是将查询结果仅仅一次性返回一个结果集用于外部查询调用。使用方式和视图，子查询一样

 

;WITH cte_test(a,b) AS(
    SELECT col1,col2 FROM table
)
SELECT a,b FROM cte_test

公用表表达式可以在接下来一条语句中多次 引用

SELECT a,b FROM cte_test t1 INNER JOIN cte_test t2 ON t1.a = t2.b

只能在接下来的一条语句使用，意味着作用域只有下一条语句

可以定义多个CTE

;WITH cte_test(a,b) AS(

    SELECT col1,col2 FROM table

)

,cte_test2(aa,bb) AS (

    SELECT a.col1,b.col2 FROM cte_test a INNER JOIN table2 b ON a.col1 = b.col1

)

SELECT aa,bb FROM cte_test2