存档

文章标签 ‘CTE’

Microsoft SQL Server 2008中T-SQL对CTE的增强(续)

2009年11月20日
没有评论

　　CTEs的真正强大之处在于对树状结构的数据上执行的分层(hierarchical)递归查询。实事上，除了符合ANSI SQL-92标准之外，这也是Microsoft构建CTEs的主要原因。一个递归CTE同至少两部分查询组成:首先，锚成员(anchor member)是一个非递归查询；第二个，递归成员(recursive member)是一个递归查询。在CTE定义的括号里(AS语句之后),查询定义为独立的或引用同一CTE。锚成员或递归成员由UNION ALL语句分开。锚成员只会被调用一次；递归成员会被重复调用，直到没有行被返回。下面是语法:

WITH SimpleRecursive(field names)
AS
(
   <Select Statement for the Anchor Member>

   UNION ALL

   <Select Statement for the Recursive Member>
)

SELECT * FROM SimpleRecursive

下面的代码将演示这些特性。下面的代码创建了一个employees表，表中有一个自引用到EmployeeID的字段：ReportsTo。我们将编写一个查询得到所有向Stephen (EmployeeID=2)报告的员工及向Stephen下级报告的员工。

 测试表：

CREATE TABLE EmployeeTree
 (EmployeeID int PRIMARY KEY,
  EmployeeName nvarchar(50),
  ReportsTo int)
GO

--insert some data, build a reporting tree
INSERT INTO EmployeeTree VALUES(1, 'Richard', NULL)
INSERT INTO EmployeeTree VALUES(2, 'Stephen', 1)
INSERT INTO EmployeeTree VALUES(3, 'Clemens', 2)
INSERT INTO EmployeeTree VALUES(4, 'Malek', 2)
INSERT INTO EmployeeTree VALUES(5, 'Goksin', 4)
INSERT INTO EmployeeTree VALUES(6, 'Kimberly', 1)
INSERT INTO EmployeeTree VALUES(7, 'Ramesh', 5)

 递归查询代码：

WITH SimpleRecursive(EmployeeID, EmployeeName, ReportsTo)
AS
(
  SELECT EmployeeID, EmployeeName, ReportsTo
   FROM EmployeeTree WHERE EmployeeID = 2
  UNION ALL
  SELECT p.EmployeeID, p.EmployeeName, p.ReportsTo
   FROM EmployeeTree AS P
    INNER JOIN SimpleRecursive A ON A.EmployeeID = P.ReportsTo
)
SELECT sr.EmployeeName AS Employee, et.EmployeeName AS Boss
 FROM SimpleRecursive AS sr
  INNER JOIN EmployeeTree AS et ON sr.ReportsTo = et.EmployeeID

下面是查询结果：

Employee    Boss
----------- ------------
Stephen     Richard
Clemens     Stephen
Malek       Stephen
Goksin      Malek
Ramesh      Goskin

　　递归查询以EmployeeID = 2开始(SELECT之后的锚成员),此查询将获取相应的记录，然后由递归查询获取向Stephen报告的员工记录及相应记录的下级。(例如Goksin向Malek汇报, 以及 Malek 向Stephen汇报.)。然后每个子递归尝试获取上次递归找到的员工的下级。最后，递归将返回空记录，这将导致递归结束(这就是为什么没有Kimberly记录的原因)。如果将上述代码中的的锚成员改为EmployeeID = 1，查询将返回Kimberly记录。

　　从设计上讲，递归成员将一直查询下级员工(对本例来说)并无限持续下去。如果你怀疑递归过多并想限制递归调用的次数，你可以在CTE的外查询(见下例)中指定OPTION子句的MAXRECURSION选项。如:OPTION(MAXRECURSION 25)

　　这个选项将促使SQL Server在递归深度超过指定限制时产生一个错误。缺省时，这个限制是100(就是在你忽略这个选项时)。如果你想彻底取消此选项的作用，你需要指定MAXRECURSION为０。你可以通过指定MAXRECURSION选项用和上面一样的代码来取得最上两级员工：

 

WITH SimpleRecursive(EmployeeID, EmployeeName, ReportsTo)
AS
(
  SELECT EmployeeID, EmployeeName, ReportsTo
   FROM EmployeeTree WHERE EmployeeID = 2
  UNION ALL
  SELECT p.EmployeeID, p.EmployeeName, p.ReportsTo
   FROM EmployeeTree AS P
    INNER JOIN SimpleRecursive A ON A.EmployeeID = P.ReportsTo
)
SELECT sr.EmployeeName AS Employee, et.EmployeeName AS Boss
 FROM SimpleRecursive AS sr
  INNER JOIN EmployeeTree AS et ON sr.ReportsTo = et.EmployeeID
OPTION(MAXRECURSION 2)

下面中结果:
Employee Boss
———- ————
Stephen Richard
Clemens Stephen
Malek Stephen
Goksin Malek

　　你同时会看到如下的错误信息：

Msg 530, Level 16, State 1, Line 2
The statement terminated. The maximum recursion 2 has been exhausted
before statement completion.

　    为了避免这种错误，一种方法是用一个产生列(generated column)来跟踪当前的层级并在WHERE子句中使用这个产生列作为限制条件,而不使用MAXRECURSION选项。下面是修改过的代码，它将返回和上述同样的数据，但不会产生错误信息：

 

WITH SimpleRecursive(EmployeeID, EmployeeName, ReportsTo, SubLevel)
AS
(
  SELECT EmployeeID, EmployeeName, ReportsTo, 0
   FROM EmployeeTree WHERE EmployeeID = 2
  UNION ALL
  SELECT p.EmployeeID, p.EmployeeName, p.ReportsTo, SubLevel + 1
   FROM EmployeeTree AS P
    INNER JOIN SimpleRecursive A ON A.EmployeeID = P.ReportsTo
    WHERE SubLevel <= 2
)
SELECT sr.EmployeeName AS Employee, et.EmployeeName AS Boss
 FROM SimpleRecursive sr
  INNER JOIN EmployeeTree AS et ON sr.ReportsTo = et.EmployeeID

　　另外，SQL Server 2008引入了一个hierarchyid新数据类型，可以比我们刚才看到的递归代码实现更强大树形结构数据。等有机会我们再探讨。

SQL Server , , ,