T-SQL—理解CTEs

?

在推出SQLServer2005之后，微软定义了一个新的查询架构叫做公共表表达式--CTE。CTE是一个基于简单查询的临时结果集，在一个简单的插入、更新、删除或者select语句的执行范围内使用。再本篇中，我们将看到如何定义和使用CTE。

通过使用CTE你能写和命名一个T-SQL select 语句，然后引用这个命名的语句就像使用一个表或者试图一样。

CTE下面就是定义一个CTE的语法:

WITH (Column1,Column2,…) AS (CTE Definition)

表示:

• ""?? CTE的命名
• "Column 1,…"? 查询语句返回结果集的列名称
• "CTE Definition"???????????? select语句返回的结果集.

定义CTE需要跟着一个INSERT,UPDATE,DELETE,或者SELECT的语句来引用CTE。假如CTE是一个批处理的一部分，那么语句之前用一个With开始然后以分号结束。当你定义了一个多重CTE，即一个CTE引用另一个CTE则需要被引用的CTE定义在引用的CTE之前。听起来可能有点混乱，那我们闲话少说看实例来说明吧。

下面是一些在CTE中可以被使用的选项:

• ORDER BY (当使用top的时候可以使用)
• INTO
• OPTION （带有查询提示）
• FOR XML
• FOR BROWSE

递归CTE语句

我理解递归就是调用自己的过程。每一个递归处理的迭代都返回一个结果的子集。这个递归处理保持循环调用直至达到条件限制才停止。最终的结果集其实就是CTE循环中每一个调用超生的结果集的并集。

递归CTE,包含了至少两个查询定义，一个是select语句，另一个查询被作为“锚成员”，而其他的查询定义被作为循环成员。锚成员查询定义不包含CTE而循环成员中包括。另外，锚成员查询需要出现在CTE递归成员查询之前，且两者返回的列完全相同。可以有多个锚成员查询，其中每一个都需要与UNION ALL,UNION,INTERSECT,或者 EXCEPT联合使用。当然也有多重的递归查询定义，每一个递归查询定义一定与UNION ALL联合使用。UNION ALL 操作符被用来连接最后的锚查询与第一个递归查询。接下来我们用实际立在来讨论一下CTE和递归CTE。

Example of a Simple CTE

如前所述，CTE 提供了一种能更好书写你的复杂代码的方法，提高了代码可读性。如下面的复杂的代码

接下来我们用CTE来实现上述的代码。

使用多重CTE的例子

假如你的代码更加复杂并且包含多个子查询，你就得考虑重写来简化维护和提高易读性。重写的方式之一就是讲子查询重写成CTEs。为了更好地展示，先看一下下面的非CTE复杂查询如下：

能够用一个单一的WITH 子句定义一个多重CTEs,然后包括这些CTEs在我的最中的TSQL语句中，这使得我可以更容易的读、开发和调试。使用多重CTEs对于复杂的TSQL逻辑而言，让我们将代码放到更容易管理的细小部分里面分隔管理。

CTE引用CTE

为了实现CTE引用另一个CTE我们需要满足下面两个条件:

1. 被定义在同一个WITH自居中作为CTE被引用
2. 被定义在被引用的CTE后面

代码如下：

Sales AS ( SELECT SalesPersonID,SUM(TotalDue) AS TotalSales,YEAR(OrderDate) AS SalesYear FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL GROUP BY SalesPersonID,YEAR(OrderDate) ),-- 第二个子查询引用第一个CTETotalSales AS ( SELECT SUM(TotalSales)AS TotalSales,SalesYear FROM Sales GROUP BY SalesYear ) -- 调用第二个CTE TotalSales ORDER BY SalesYear;

这个代码中，我定义了一个CTE命名为Sales ，被第二个CTE引用，定义第二个CTE叫做TotalSales，在这个CTE? 中我汇总了TotalSales 列，通过组合SalesYear列。最后我使用Select语句引用第二个CTE。

CTE递归调用CTE实例

另一个CTE的主要功能就是调用自己，当用CTE调用自己的时候，就行程了CTE递归调用。一个递归CTE有两个主要部分，一个是锚成员，一个是递归成员。锚成员开启递归成员，这里你可以把锚成员查询当做一个没有引用CTE的查询。而递归成员将会引用CTE。这个锚成员确定了初始的记录集，然后递归成员来使用这个初始记录集。为了更好地理解递归CTE，我将创建一个实例数据通过使用递归CTE，

下面就是代码Listing 6：

Listing 6

在Listing 6我创建了一个员工表，包含了员工信息，这个表中插入了9个不同的员工，MgrId 字段用来区分员工的领导的ID，这里有一个字段为null的记录。这个人没有领导且是这里的最高级领导。来看看我将如何使用递归CTE吧，在Listing7中：

Listing 7

执行脚本结果：

MgrID EmpID  EmpName        Position                   OrgLevel
----- ------ -------------- -------------------------- -----------
NULL  1      Joe Steel      President                  0
1     2      John Smith     VP Western Region Sales    1
1     3      Sue Jones      VP Easter Region           1
1     6      Kathy Johnson  Admin Assistant            1
2     4      Lynn Holland   Sales Person               2
2     8      David Nelson   Sales Person               2
3     5      Linda Thomas   Sales Person               2
3     7      Rich Little    Sales Person               2
3     9      Mary Jackson   Sales Person               2

我们能发现这个结果是所有员工分级结构，注意OrgLevel 字段确定了分层等级结构，当你看到0的时候说明这个人就是最大的领导了，每一个员工过的直属领导都比自己的OrgLevel 大1。

控制递归

有时候会出现无穷递归的CTE的可能，但是SQLServer有一个默认的最大递归值来避免出现无限循环的CTE递归。默认是100，下面我来举例说明：

这部分代码引发了一个无限循环，因为递归部分将永远返回多行数据，这部分查询返回的结果是MrgID 为1的结果。而我去运行这段代码后，只循环了100次，这就是由于最大递归次数的默认为100。当然这个值也是可以设定的。假如我们打算超过100次，150次的话，如下所示：

通过设定MAXRECUSION 的值为150次实现了递归150次的最大递归限制，这个属性的最大值为32,767。

何时使用CTE

当然我们学习了如何使用CTE就要知道什么时候来使用它，下面三种情况是使用CTE简化你的T-SQL语句的情况：

1. 查询中需要递归
2. 查询中有多个子查询，或者你有重复的相同的子查询在单一语句中。
3. 查询时复杂庞大的

总结

CTE的功能为SQLServer 提供了强大的补充，它让我们可以将复杂的代码切成很多易于管理和读取的小的代码段，同时还允许我们使用它来建立递归代码。CTE提供了另一种方法来实现复杂的T-SQL逻辑，为将来我们的开发提供了非常好的代码规范和易读性，

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!