FastReport教程：MS SQL中的递归

下载FastReport.Net最新版本

有时，需要存储过程或函数才能多次使用样本的结果。在这种情况下，我们经常使用临时表。但是，值得考虑临时表的一些优点和缺点。

好处：

• 临时表是完整的表。因此，您可以为它们创建索引和统计信息。这可以显着加快他们的工作。

缺点：

• 填写与数据移动相关的临时表。虽然这是一个简单的插入操作，但磁盘上仍然存在大量数据的负载;
• 存在查询执行时间增加的风险。临时表在tempdb数据库中创建。而且这个基地的负荷很大。

考虑到使用临时表的风险，使用通用表表达式看起来更具吸引力。

通用表表达式

公用表表达式（CTE）是一个带有公用表的表达式，可以在查询中多次使用。CTE不会保存数据，但会创建类似临时视图的内容。有人可能会说CTE是主查询之前的子查询。但这并不完全正确，因为子查询不能多次使用，但是，CTE可以。

在哪些情况下使用通用表表达式更好？

1. 创建递归查询，使用它可以以分层形式获取数据;
2. 在同一查询中多次引用数据集;
3. 为了替换视图，临时表，表变量。

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中递归树遍历的最佳解决方案。