有时，需要存储过程或函数才能多次使用样本的结果。在这种情况下，我们经常使用临时表。但是，值得考虑临时表的一些优点和缺点。
好处：
临时表是完整的表。因此，您可以为它们创建索引和统计信息。这可以显着加快他们的工作。
缺点：
填写与数据移动相关的临时表。虽然这是一个简单的插入操作，但磁盘上仍然存在大量数据的负载;
存在查询执行时间增加的风险。临时表在tempdb数据库中创建。而且这个基地的负荷很大。
考虑到使用临时表的风险，使用通用表表达式看起来更具吸引力。
通用表表达式
公用表表达式（CTE）是一个带有公用表的表达式，可以在查询中多次使用。CTE不会保存数据，但会创建类似临时视图的内容。有人可能会说CTE是主查询之前的子查询。但这并不完全正确，因为子查询不能多次使用，但是，CTE可以。
在哪些情况下使用通用表表达式更好？
创建递归查询，使用它可以以分层形式获取数据;
在同一查询中多次引用数据集;
为了替换视图，临时表，表变量。
CTE的优点包括：递归，高速查询，简洁查询。
缺点只能在有限的使用中。CTE只能用于它所属的查询。您不能在其他查询中使用它。在这种情况下，您将不得不使用临时表或表变量。 通用表表达式简单且递归。 简单的不包括对自己的引用，并且递归分别包括。 递归CTE用于返回分层数据。 考虑一个简单CTE语句的示例：
WITH CTEQuery (Field1, Field2) AS ( SELECT (Field1, Field2) FROM TABLE ) SELECT * FROM CTEQuery
这里CTEQuery是CTE的名称;
Field1，Field2 - 请求的字段名称;
Table - 从中选择数据以在主查询中使用的一些表。
在此示例中，可以而不是显式指定选择字段，因为我们从TestTable表中选择所有字段：
WITH CTEQuery AS ( SELECT * FROM Table ) SELECT * FROM CTEQuery
在CTE的帮助下，如果取出CTE中的部分逻辑，则可以优化主查询。事实是，CTE允许您一次创建多个表达式（查询）。因此，您可以使用CTE将复杂查询拆分为几个初步“View”，然后将它们链接到一个公共查询中：
WITH CTEQuery1 (Field1, Field2) AS ( SELECT Field1 AS ID, Field2 FROM Table1 WHERE Field2 >= 1000 ), CTEQuery2 (Field3, Field4) AS ( SELECT Field3 AS ID, Field4 FROM Table2 WHERE Field4 = 'Москва' ) SELECT * FROM CTEQuery1 INNER JOIN CTEQuery2 ON CTEQuery2.ID = CTEQuery1.ID
如上所述，CTE的主要目的是递归。递归的典型任务是树遍历。所以我们可以在“with”的帮助下构建一棵树。递归查询结构首先出现在SQL Server 2005中。 看一下WITH语句：
WITH RecursiveQuery AS
(
{Anchor}
UNION ALL
{Joined TO RecursiveQuery}
)
SELECT * FROM RecursiveQuery
{Anchor} - anchor，一个定义树的初始元素的查询（分层列表）。通常在锚中有一个WHERE子句，用于定义表的特定行。 在UNION ALL之后，目标表从JOIN跟随到CTE表达式。 {加入RecursiveQuery} - 从目标表中选择。这通常与锚点中使用的表相同。但是在这个查询中，它连接到CTE表达式，形成递归。此连接的条件决定了父子关系。这取决于你是去树的上层还是下层。 让我们看一个返回组织单元列表的递归查询。准备此请求的数据：
CREATE TABLE Department ( ID INT, ParentID INT, Name VARCHAR(50) ) INSERT INTO Department ( ID, ParentID, Name ) VALUES (1, 0, 'Finance Director') INSERT INTO Department ( ID, ParentID, Name ) VALUES (2, 1, 'Deputy Finance Director') INSERT INTO Department ( ID, ParentID, Name ) VALUES (3, 1, 'Assistance Finance Director') INSERT INTO Department ( ID, ParentID, Name ) VALUES (4, 3, 'Executive Bodget Office') INSERT INTO Department ( ID, ParentID, Name ) VALUES (5, 3, 'Comptroller') INSERT INTO Department ( ID, ParentID, Name ) VALUES (6, 3, 'Purchasing') INSERT INTO Department ( ID, ParentID, Name ) VALUES (7, 3, 'Debt Management') INSERT INTO Department ( ID, ParentID, Name ) VALUES (8, 3, 'Risk Management') INSERT INTO Department ( ID, ParentID, Name ) VALUES (9, 2, 'Public Relations') INSERT INTO Department ( ID, ParentID, Name ) VALUES (10, 2, 'Finance Personnel') INSERT INTO Department ( ID, ParentID, Name ) VALUES (11, 2, 'Finance Accounting') INSERT INTO Department ( ID, ParentID, Name ) VALUES (12, 2, 'Liasion to Boards and Commissions')
已经清楚的是，组织中的分支结构是分层的。我们的任务是获得一份隶属于财务总监助理的部门清单。如果我们在分层树的上下文中进行讨论，那么我们必须找到一个分支及其叶子。 但首先，让我们看看整个分部列表：
ID |
ParentID |
Name |
1 |
0 |
Finance Director |
2 |
1 |
Deputy Finance Director |
3 |
1 |
Assistance Finance Director |
4 |
3 |
Executive Bodget Office |
5 |
3 |
Comptroller |
6 |
3 |
Purchasing |
7 |
3 |
Debt Management |
8 |
3 |
Risk Management |
9 |
2 |
Public Relations |
10 |
2 |
Finance Personnel |
11 |
2 |
Finance Accounting |
12 |
2 |
Liasion to Boards and Commissions |
头部有财务总监，副手和助理报表给他。他们每个人在其管辖范围内都有一组单位。ParentID字段指示“主机”标识符。因此，我们有一个现成的主从连接。 让我们用WITH编写一个递归查询。
WITH RecursiveQuery (ID, ParentID, Name) AS ( SELECT ID, ParentID, Name FROM Department dep WHERE dep.ID = 3 UNION ALL SELECT dep.ID, dep.ParentID, dep.Name FROM Department dep JOIN RecursiveQuery rec ON dep.ParentID = rec.ID ) SELECT ID, ParentID, Name FROM RecursiveQuery
在此示例中，清楚地指示了要在CTE中选择的字段的名称。但是，内部查询具有相同的字段。因此，您只需删除此列表以及括号即可。 在CTE内部，我们有两个类似的查询。第一个选择我们正在构建的树的根元素。第二个是所有后续的从属元素，因为它与CTE本身有关。SQL中的“递归”实际上不是递归，而是迭代。您需要以JOIN作为循环提交查询，然后一切都将立即清除。在每次迭代中，我们都知道前一个样本的值并获得从属元素。在下一步中，我们获得前一个样本的从属元素。也就是说，每次迭代都是向下或向上转换，具体取决于通信条件。 上述查询的结果是：
ID |
ParentID |
Name |
3 |
1 |
Assistance Finance Director |
4 |
3 |
Executive Bodget Office |
5 |
3 |
Comptroller |
6 |
3 |
Purchasing |
7 |
3 |
Debt Management |
8 |
3 |
Risk Management |
但是如果不使用CTE，这个查询会是什么样子：
DECLARE @Department TABLE (ID INT, ParentID INT, Name VARCHAR(50), Status INT DEFAULT 0) -- First, we select the anchor in the table variable - the initial element from which we build the tree. INSERT @Department SELECT ID, ParentID, Name, 0 FROM Department dep WHERE dep.ID = 3 DECLARE @rowsAdded INT = @@ROWCOUNT -- We are going through a cycle until new departments are added in the previous step. WHILE @rowsAdded > 0 BEGIN -- Mark entries in a table variable as ready for processing UPDATE @Department SET Status = 1 WHERE Status = 0 -- Select child records for the previous record INSERT @Department SELECT dep.ID, dep.ParentID, dep.Name, 0 FROM Department dep JOIN @Department rec ON dep.ParentID = rec.ID AND rec.Status = 1 SET @rowsAdded = @@ROWCOUNT -- Mark entries found in the current step as processed UPDATE @Department SET Status = 2 WHERE Status = 1 END SELECT * FROM @Department
这样的循环比CTE表达慢得多。此外，它需要创建一个表变量。并且代码量增加了一倍。因此，CTE表达式是MS SQL中递归树遍历的最佳解决方案。