【译】高级T-SQL进阶系列 （三）【上篇】：理解公共表表达式（CTEs）

【译注：此文为翻译，由于本人水平所限，疏漏在所难免，欢迎探讨指正】

原文链接：传送门。

 

伴随着SQL SERVER 2005的首次展示，微软介绍了一种新的被称为“公共表 表达式”（CTE）的查询结构。一个CTE是由一个简单查询定义的临时结果集，并且用在一个单独的 INSERT/UPDATE/DELETE/SELECT的作用域中。在这篇文章中，我将介绍如何定义以及使用 CTEs。

定义及使用CTEs

随着微软对CTEs的介绍，现在你有了一种不同的方式来构造和书写复杂的TSQL代码了。通过使用一个CTE你可以书写并且命名一个TSQL SELECT语句，并且随后便可以引用这个命名的TSQL，如同你就是在引用一个表或者视图一般。

下面是用来定义一个CTE的句法：

WITH <expression_name> (Column1, Column2, …) AS (CTE Definition)

在这里：

• <expression_name> 是CTE的名字。

• (Column1, Column2, …)是从CTE返回的列的名字（只有当列名没有提供，或者不是唯一的时候才是必须的）。

• (CTE Definition)是一个SELECT语句，其定义了CTE的结果集。

一个CTE定义后面需要跟着一个单独的 INSERT/UPDATE/DELETE/SELECT语句，且其引用了这个命名的CTE。如果一个CTE是一个批处理语句的一部分，那么WITH子句前面的语句必须以一个分号结尾。

 当你定义多个CTE时候，引用了另一个CTE语句的CTE必须在其所引用 的CTE语句之后进行定义。

如下是一些不能在CTE中使用的子句：

• ORDER BY (unless used with TOP clause)
• INTO
• OPTION clause with query hints
• FOR XML
• FOR BROWSE

 递归CTE

递归处理是调用自身的一个处理过程。递归过程的每一次迭代都会返回结果集的一部分。递归过程会不断的调用自身直到找到了一个条件，其预示着不应该再调用自身，此时，递归结束。在SQL SERVER中，递归CTE的最终结果集是每次调用递归查询的所有结果集的总和。

一个CTE可以引用其自身，当这个过程结束时它便是一个递归CTE。一个递归CTE至少包含两个查询定义，其都是SELECT语句。一个查询定义被称为锚点成员，而另一个查询定义被成为递归成员。一个锚点成员查询定义不会引用一个CTE而一个递归成员会引用一个CTE。除此之外，一个锚点成员查询在一个CTE中会先于一个递归成员查询而出现。从锚点成员返回的列的数目必须要和从递归成员返回的列的数目保持一致。可以有多个锚点查询定义，它们使用这些操作符中的一个进行组合：UNION ALL, UNION, INTERSECT, or EXCEPT。同样也可以有多个递归查询定义。每一个递归查询定义必须要用UNION ALL操作符组合起来。UNION ALL操作符同样也用来连接最后一个锚点查询定义与第一个递归查询定义。同样的，当我们看一些例子时这会变得更加清晰。

 

对于CTE以及递归CTE我们已经做了足够的讨论，现在让我们来看一些CTE的例子。

To be continued...