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

上一篇:PHP代码审计入门(敏感函数回溯参数过程)


下一篇:C#之生成树形菜单数据