Common Table Expressions (CTE)

子查询有时使用起来很麻烦，因为所有的过滤和匹配逻辑都必须集成到子查询表达式中。如果只需要执行一个任务，且只需要使用一次杳询表达式，子查询是很好的选择。但子查询不能被重用，也不能很好地支持多个需求。这个问题的一个常见解决方法是用子查询的内容填充一个临时表，连接和过滤表达式可以应用于这个临时表，但性能很差，且会消耗大量的系统资源，使用临时表还需要创建和使用另一个表的权限。

CTE是这两个问题的最佳解决方法：它是一个只存在于内存的子查询，所以不需要特殊的权限，也不需要物理空间操作。CTE与传统的子查询不同，它是一个已命名的对象，可以像表那样重用和引用，并且灵活性高得多。CTE需在查询脚本中被使用之前进行定义，其定义形式是：用WITH开头，后跟一列放在括号中的输出列，之后是关键字AS和一个放在括号中的完整SELECT语句。

WITH <expression_name> [ ( column_name [ , ... n ] ) ]
    AS
    ( CTE_query_definition )
    [ , < another_expresstion > ]
<query>

首先为CTE提供一个名称，该名称类似于派生表的别名。然后可以提供CTE将返回的列表名称；如果CTE指定了它的所有返回列，则这是可选操作。最后，在圆括号中添加CTE查询的定义，然后添加使用CTE的主查询。

---

以下内容来自： 逆心（博客园）

WITH AS的含义

WITH AS-做子查询部分(subquery factoring)。

它用于定义一个SQL片段，该片段会被是整个SQL语句所用到。如果WITH AS所以定的表名被调用两次以上，则优化器会自动将WITH AS所获取的数据放入临时表里，如果只是被调用一次，则不会。可以通过materialize将WITH AS短语里的数据强制放入全局临时表里。

WITH AS可以被紧跟着的一条SQL语句所使用多次，但不能被紧跟着的多条SQL语句使用。

CTE的定义

根据微软对CTE好处的描述，可以归结为四点:

• 可以定义递归公用表表达式(CTE)
• 当不需要将结果集作为视图被多个地方引用时，CTE可以使其更加简洁
• GROUP BY语句可以直接作用于子查询所得的标量列
• 可以在一个语句中多次引用公用表表达式(CTE)

按照是否递归，可以将公用表（CTE）表达式分为递归公用表表达式和非递归公用表表达式.

非递归公用表表达式(CTE)：

非递归公用表表达式（CTE）是查询结果仅仅一次性返回一个结果集用于外部查询调用。并不在其定义的语句中调用其自身的CTE。

非递归公用表表达式（CTE）的使用方式和视图以及子查询一致。

比如一个简单的非递归公用表表达式:

WITH CTE_Test
AS
(
    SELECT * FROM Person_1
)
SELECT * FROM CTE_Test

公用表表达式的好处之一是可以在接下来一条语句中多次引用:

WITH CTE_Test
　　AS
　　(
    　　SELECT * FROM Person_1
　　)
　　SELECT * FROM CTE_Test AS a　　--第一次引用
　　INNER JOIN  CTE_Test AS b　　　 --第二次引用
　　ON a.Id = b.Id
　　ORDER BY a.Id DESC

虽然以上引用了多次，但是只是一条语句，所以可以正常执行。

如果多条语句引用，如下面这样，是会报错的。

WITH CTE_Test
　　AS
　　(
    　　SELECT * FROM Person_1
　　)
　　SELECT * FROM CTE_Test 
　　SELECT * FROM CTE_Test

输出结果如下：

由于CTE只能在接下来一条语句中使用，因此，当需要接下来的一条语句中引用多个CTE时，可以定义多个，中间用逗号分隔,下面是一次定义多个CTE的例子：

WITH CTE_Test1
AS
(
SELECT * FROM Person_1
),
CTE_Test2
AS
(
SELECT * FROM Person_2
)
SELECT * FROM CTE_Test1
UNION
SELECT * FROM CTE_Test2

结果如下：

递归公用表表达式(CTE)：

    对于递归公用表达式来说，只需要在语句中定义两部分：

•    基本语句
•    递归语句

   先建一张表栏目表如下，栏目Id，栏目名称，栏目的父栏目。

现在使用CTE查询其每个栏目是第几层栏目的代码如下：

WITH COL_CTE(Id,Name,ParentId,tLevel )
AS
(
    --基本语句
    SELECT Id,Name,ParentId,0 AS tLevel FROM Col
    WHERE ParentId = 0
    UNION ALL
    --递归语句
    SELECT c.Id,c.Name,c.ParentId,ce.tLevel+1 AS tLevel FROM COL as c 
    INNER JOIN COL_CTE AS ce 　　--递归调用
    ON c.ParentId = ce.Id
)
SELECT * FROM COL_CTE

输出结果如下：

0表示顶级栏目。1就是1级栏目。语法非常优雅。就一个SELECT * FRON COL_CTE。这正是CTE强大的地方，但是，这要有约束，否则如果无限制递归可以会消耗掉非常多的系统资源。下面来看看如何限制递归的最大次数。

如将上面的查询语法改为：

WITH COL_CTE(Id,Name,ParentId,tLevel )
AS
(
    --基本语句
    SELECT Id,Name,ParentId,0 AS tLevel FROM Col
    WHERE ParentId = 0
    UNION ALL
    --递归语句
    SELECT c.Id,c.Name,c.ParentId,ce.tLevel+1 AS tLevel FROM COL as c 
    INNER JOIN COL_CTE AS ce 
    ON c.ParentId = ce.Id
)
SELECT * FROM COL_CTE

OPTION(MAXRECURSION 2)　　--指定最大递归次数为2

我们知道在上面的查询中，要查到天河区新闻最少要递归3次，但是现在只递归2次，运行是什么结果呢？

提示信息如下：

消息 530，级别 16，状态 1，第 1 行
语句被终止。完成执行语句前已用完最大递归 2。