Common Table Expressions(公用表表达式 CTE)

2008-03-05 12:32 Valens 阅读(1050) 评论(0) 编辑收藏举报

索引：
1、使用公用表表达式；
2、使用公用表表达式的递归查询；
3、WITH common_table_expression (Transact-SQL)。

使用公用表表达式

公用表表达式 (CTE) 可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。CTE 与派生表类似，具体表现在不存储为对象，并且只在查询期间有效。与派生表的不同之处在于，CTE 可自引用，还可在同一查询中引用多次。

CET 可用于：

创建递归查询。有关详细信息，请参阅使用公用表表达式的递归查询。
在不需要常规使用视图时替换视图，也就是说，不必将定义存储在元数据中。
启用按从标量嵌套 select 语句派生的列进行分组，或者按不确定性函数或有外部访问的函数进行分组。
在同一语句中多次引用生成的表。

使用 CTE 可以获得提高可读性和轻松维护复杂查询的优点。查询可以分为单独块、简单块、逻辑生成块。之后，这些简单块可用于生成更复杂的临时 CTE，直到生成最终结果集。

可以在用户定义的例程（如函数、存储过程、触发器或视图）中定义 CTE。

CTE 的结构

CTE 由表示 CTE 的表达式名称、可选列列表和定义 CET 的查询组成。定义 CTE 后，可以在 SELECT、INSERT、UPDATE 或 DELETE 语句中对其进行引用，就像引用表或视图一样。CTE 也可用于 CREATE VIEW 语句，作为定义 SELECT 语句的一部分。

CTE 的基本语法结构如下：

WITH expression_name [ ( column_name [,

n] ) ]

AS

( CTE_query_definition )

只有在查询定义中为所有结果列都提供了不同的名称时，列名称列表才是可选的。

运行 CTE 的语句为：

SELECT <column_list>

FROM expression_name

示例

下面的示例显示了 CTE 结构的组件：表达式名称、列列表和查询。CTE 表达式 Sales_CTE 包含三个列（SalesPersonID、NumberOfOrders 和 MaxDate），在每个销售人员 SalesOrderHeader 表中被定义为销售订单总数和最近销售订单日期。执行语句时，CTE 将被引用两次：一次返回为销售人员所选的列，另一次检索销售经理的类似详细信息。销售人员和销售经理的数据都返回在一行中。

USE AdventureWorks;
GO
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
    SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
    E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
    JOIN Sales_CTE AS OS
    ON E.EmployeeID = OS.SalesPersonID
    LEFT OUTER JOIN Sales_CTE AS OM
    ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO

以下为部分结果集：

EmployeeID  NumberOfOrders MaxDate  ManagerID NumberOfOrders MaxDate

----------- -------------- ---------- --------- -------------- ----------

268         48             2004-06-01 273       NULL           NULL

275         450            2004-06-01 268       48             2004-06-01

276         418            2004-06-01 268       48             2004-06-01

277         473            2004-06-01 268       48             2004-06-01

使用公用表表达式的递归查询

公用表表达式 (CTE) 具有一个重要的优点，那就是能够引用其自身，从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。

在 SQL Server 2005 中，当某个查询引用递归 CTE 时，它即被称为“递归查询”。递归查询通常用于返回分层数据，例如：显示某个组织图中的雇员或物料清单方案（其中父级产品有一个或多个组件，而那些组件可能还有子组件，或者是其他父级产品的组件）中的数据。

递归 CTE 可以极大地简化在 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句中运行递归查询所需的代码。在 SQL Server 的早期版本中，递归查询通常需要使用临时表、游标和逻辑来控制递归步骤流。有关公用表表达式的详细信息，请参阅使用公用表表达式。

递归 CTE 的结构

Transact-SQL 中的递归 CTE 的结构与其他编程语言中的递归例程相似。尽管其他语言中的递归例程返回标量值，但递归 CTE 可以返回多行。

递归 CTE 由下列三个元素组成：

例程的调用。
递归 CTE 的第一个调用包括一个或多个由 UNION ALL、UNION、EXCEPT 或 INTERSECT 运算符联接的 CTE_query_definitions。由于这些查询定义形成了 CTE 结构的基准结果集，所以它们被称为“定位点成员”。
CTE_query_definitions 被视为定位点成员，除非它们引用了 CTE 本身。所有定位点成员查询定义必须放置在第一个递归成员定义之前，而且必须使用 UNION ALL 运算符联接最后一个定位点成员和第一个递归成员。
例程的递归调用。
递归调用包括一个或多个由引用 CTE 本身的 UNION ALL 运算符联接的 CTE_query_definitions。这些查询定义被称为“递归成员”。
终止检查。
终止检查是隐式的；当上一个调用中未返回行时，递归将停止。

注意：
如果递归 CTE 组合不正确，可能会导致无限循环。例如，如果递归成员查询定义对父列和子列返回相同的值，则会造成无限循环。在测试递归查询的结果时，可以通过在 INSERT、UPDATE、DELETE 或 SELECT 语句的 OPTION 子句中使用 MAXRECURSION 提示和 0 到 32,767 之间的值，来限制特定语句允许的递归级数。有关详细信息，请参阅查询提示 (Transact-SQL) 和 WITH common_table_expression (Transact-SQL)。

注意：

如果递归 CTE 组合不正确，可能会导致无限循环。例如，如果递归成员查询定义对父列和子列返回相同的值，则会造成无限循环。在测试递归查询的结果时，可以通过在 INSERT、UPDATE、DELETE 或 SELECT 语句的 OPTION 子句中使用 MAXRECURSION 提示和 0 到 32,767 之间的值，来限制特定语句允许的递归级数。有关详细信息，请参阅查询提示 (Transact-SQL) 和 WITH common_table_expression (Transact-SQL)。

伪代码和语义

递归 CTE 结构必须至少包含一个定位点成员和一个递归成员。以下伪代码显示了包含一个定位点成员和一个递归成员的简单递归 CTE 的组件。

WITH cte_name ( column_name [,

n] )

AS

(

CTE_query_definition –- Anchor member is defined.

UNION ALL

CTE_query_definition –- Recursive member is defined referencing cte_name.

)

-- Statement using the CTE

SELECT *

FROM cte_name

递归执行的语义如下：

将 CTE 表达式拆分为定位点成员和递归成员。
运行定位点成员，创建第一个调用或基准结果集 (T₀)。
运行递归成员，将 T_i 作为输入，将 T_i+1 作为输出。
重复步骤 3，直到返回空集。
返回结果集。这是对 T₀ 到 T_n 执行 UNION ALL 的结果。

示例

以下示例通过返回 Adventure Works Cycles 公司的雇员的分层列表（从最高级雇员开始）显示递归 CTE 结构的语义。执行 CTE 的语句将结果集限制到研发组中的雇员。示例后面是代码执行的演练。

USE AdventureWorks;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
-- Anchor member definition
    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
        0 AS Level
    FROM HumanResources.Employee AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
    WHERE ManagerID IS NULL
    UNION ALL
-- Recursive member definition
    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
        Level + 1
    FROM HumanResources.Employee AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
    INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
    ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Research and Development' OR Level = 0;
GO

示例代码演练

递归 CTE DirectReports 定义了一个定位点成员和一个递归成员。

定位点成员返回基准结果集 T₀。这就是公司中的最高级雇员，即不向经理报告的雇员。
以下是定位点成员返回的结果集：

ManagerID EmployeeID Title                                   Level

    --------- ---------- --------------------------------------- ------

    NULL      109        Chief Executive Officer                 0

递归成员返回定位点成员结果集中的雇员的直接下属。这是通过在 Employee 表和 DirectReports CTE 之间执行联接操作获得的。正是此次对 CTE 自身的引用建立了递归调用。利用 CTE DirectReports 中的雇员作为输入 (T_i)，联接 (Employee.ManagerID = DirectReports.EmployeeID) 返回经理为 (T_i) 的雇员作为输出 (T_i+1)。这样，递归成员的第一次迭代返回了以下结果集：
```
ManagerID EmployeeID Title                                   Level

    --------- ---------- --------------------------------------- ------

    109       12         Vice President of Engineering           1
```

重复激活递归成员。递归成员的第二次迭代使用步骤 3 中的单行结果集（包含 EmployeeID 12）作为输入值，并返回以下结果集：

ManagerID EmployeeID Title                                   Level

    --------- ---------- --------------------------------------- ------

    12        3          Engineering Manager                     2

递归成员的第三次迭代使用上面的单行结果集（包含 EmployeeID 3)）作为输入值，并返回以下结果集：

ManagerID EmployeeID Title                                   Level

    --------- ---------- --------------------------------------- ------

    3         4          Senior Tool Designer                    3

    3         9          Design Engineer                         3

    3         11         Design Engineer                         3

    3         158        Research and Development Manager        3

    3         263        Senior Tool Designer                    3

    3         267        Senior Design Engineer                  3

    3         270        Design Engineer                         3

递归成员的第四次迭代使用 EmployeeID 值 4、9、11、158、263、267 和 270 的上一个行集作为输入值。
重复此过程，直到递归成员返回一个空结果集。

正在运行的查询返回的最终结果集是定位点成员和递归成员生成的所有结果集的并集。
以下是示例返回的完整结果集：

ManagerID EmployeeID Title                                   Level

    --------- ---------- --------------------------------------- ------

    NULL      109        Chief Executive Officer                 0

    109       12         Vice President of Engineering           1

    12        3          Engineering Manager                     2

    3         4          Senior Tool Designer                    3

    3         9          Design Engineer                         3

    3         11         Design Engineer                         3

    3         158        Research and Development Manager        3

    3         263        Senior Tool Designer                    3

    3         267        Senior Design Engineer                  3

    3         270        Design Engineer                         3

    263       5          Tool Designer                           4

    263       265        Tool Designer                           4

    158       79         Research and Development Engineer       4

    158       114        Research and Development Engineer       4

    158       217        Research and Development Manager        4

    (15 row(s) affected)

WITH common_table_expression (Transact-SQL)

指定临时命名的结果集，这些结果集称为公用表表达式 (CTE)。该表达式源自简单查询，并且在单条 SELECT、INSERT、UPDATE 或 DELETE 语句的执行范围内定义。该子句也可用在 CREATE VIEW 语句中，作为该语句的 SELECT 定义语句的一部分。公用表表达式可以包括对自身的引用。这种表达式称为递归公用表表达式。

Transact-SQL 语法约定

语法

[ WITH <common_table_expression> [ ,...n ] ]



<common_table_expression>::=

        expression_name [ ( column_name [ ,...n ] ) ]

    AS

        ( CTE_query_definition )

参数

expression_name: 公用表表达式的有效标识符。 expression_name 必须与在同一 WITH <common_table_expression> 子句中定义的任何其他公用表表达式的名称不同，但 expression_name 可以与基表或基视图的名称相同。在查询中对 expression_name 的任何引用都会使用公用表表达式，而不使用基对象。

column_name: 在公用表表达式中指定列名。在一个 CTE 定义中不允许出现重复的名称。指定的列名数必须与 CTE_query_definition 结果集中列数匹配。只有在查询定义中为所有结果列都提供了不同的名称时，列名称列表才是可选的。

CTE_query_definition

指定一个其结果集填充公用表表达式的 SELECT 语句。除了 CTE 不能定义另一个 CTE 以外，CTE_query_definition 的 SELECT 语句必须满足与创建视图时相同的要求。有关详细信息，请参阅“备注”部分和 CREATE VIEW (Transact-SQL)。

如果定义了多个 CTE_query_definition，则这些查询定义必须用下列一个集合运算符联接起来：UNION ALL、UNION、EXCEPT 或 INTERSECT。有关如何使用递归 CTE 查询定义的详细信息，请参阅下面的“备注”部分和使用公用表表达式的递归查询。

备注

创建和使用 CTE 的指南

下列指南应用于非递归 CTE。有关适用于递归 CTE 的指南，请参阅后面的“定义和使用递归 CTE 的指南”。

CTE 之后必须跟随引用部分或全部 CTE 列的单条 SELECT、INSERT、UPDATE 或 DELETE 语句。也可以在 CREATE VIEW 语句中将 CTE 指定为视图中 SELECT 定义语句的一部分。
可以在非递归 CTE 中定义多个 CTE 查询定义。定义必须与以下集合运算符之一结合使用：UNION ALL、UNION、INTERSECT 或 EXCEPT。
CTE 可以引用自身，也可以引用在同一 WITH 子句中预先定义的 CTE。不允许前向引用。
不允许在一个 CTE 中指定多个 WITH 子句。例如，如果 CTE_query_definition 包含一个子查询，则该子查询不能包括定义另一个 CTE 的嵌套的 WITH 子句。
不能在 CTE_query_definition 中使用以下子句：
- COMPUTE 或 COMPUTE BY
- ORDER BY（除非指定了 TOP 子句）
- INTO
- 带有查询提示的 OPTION 子句
- FOR XML
- FOR BROWSE
如果将 CTE 用在属于批处理的一部分的语句中，那么在它之前的语句必须以分号结尾。
可以使用引用 CTE 的查询来定义游标。
可以在 CTE 中引用远程服务器中的表。
在执行 CTE 时，任何引用 CTE 的提示都可能与该 CTE 访问其基础表时发现的其他提示相冲突，这种冲突与引用查询中的视图的提示所发生的冲突相同。发生这种情况时，查询将返回错误。有关详细信息，请参阅视图解析。

定义和使用递归 CTE 指南

下列指南适用于定义递归 CTE 的情况：

递归 CTE 定义至少必须包含两个 CTE 查询定义，一个定位点成员和一个递归成员。可以定义多个定位点成员和递归成员；但必须将所有定位点成员查询定义置于第一个递归成员定义之前。所有 CTE 查询定义都是定位点成员，但它们引用 CTE 本身时除外。
定位点成员必须与以下集合运算符之一结合使用：UNION ALL、UNION、INTERSECT 或 EXCEPT。在最后一个定位点成员和第一个递归成员之间，以及组合多个递归成员时，只能使用 UNION ALL 集合运算符。
定位点成员和递归成员中的列数必须一致。
递归成员中列的数据类型必须与定位点成员中相应列的数据类型一致。
递归成员的 FROM 子句只能引用一次 CTE expression_name。
在递归成员的 CTE_query_definition 中不允许出现下列项：
- SELECT DISTINCT
- GROUP BY
- HAVING
- 标量聚合
- TOP
- LEFT、RIGHT、OUTER JOIN（允许出现 INNER JOIN）
- 子查询
- 应用于对 CTE_query_definition 中的 CTE 的递归引用的提示。

下列指南适用于使用递归 CTE：

无论参与的 SELECT 语句返回的列的为空性如何，递归 CTE 返回的全部列都可以为空。
如果递归 CTE 组合不正确，可能会导致无限循环。例如，如果递归成员查询定义对父列和子列返回相同的值，则会造成无限循环。可以使用 MAXRECURSION 提示以及在 INSERT、UPDATE、DELETE 或 SELECT 语句的 OPTION 子句中的一个 0 到 32,767 之间的值，来限制特定语句所允许的递归级数，以防止出现无限循环。这样就能够在解决产生循环的代码问题之前控制语句的执行。服务器范围内的默认值是 100。如果指定 0，则没有限制。每一个语句只能指定一个 MAXRECURSION 值。有关详细信息，请参阅查询提示 (Transact-SQL)。
不能使用包含递归公用表表达式的视图来更新数据。
可以使用 CTE 在查询上定义游标。CTE 是定义游标结果集的 select_statement 参数。递归 CTE 只允许使用快速只进游标和静态（快照）游标。如果在递归 CTE 中指定了其他游标类型，则该类型将转换为静态游标类型。
可以在 CTE 中引用远程服务器中的表。如果在 CTE 的递归成员中引用了远程服务器，那么将为每个远程表创建一个假脱机，这样就可以在本地反复访问这些表。

示例

A. 创建一个简单公用表表达式

以下示例显示直接向 Adventure Works Cycles 的每个经理报告的雇员的数目。

USE AdventureWorks;
GO
WITH DirReps(ManagerID, DirectReports) AS
(
    SELECT ManagerID, COUNT(*)
    FROM HumanResources.Employee AS e
    WHERE ManagerID IS NOT NULL
    GROUP BY ManagerID
)
SELECT ManagerID, DirectReports
FROM DirReps
ORDER BY ManagerID;
GO

B. 使用公用表表达式来限制次数和报告平均数

以下示例显示向经理报告的雇员的平均数。

WITH DirReps (Manager, DirectReports) AS
(
    SELECT ManagerID, COUNT(*) AS DirectReports
    FROM HumanResources.Employee
    GROUP BY ManagerID
)
SELECT AVG(DirectReports) AS [Average Number of Direct Reports]
FROM DirReps
WHERE DirectReports>= 2 ;
GO

C. 多次引用同一个公用表表达式

以下示例显示 SalesOrderHeader 表中每个销售人员的销售订单的总数和最近的销售订单的日期。CTE 在运行的语句中被引用两次：一次返回为销售人员所选的列，另一次检索销售经理的类似详细信息。销售人员和销售经理的数据都返回在一行中。

使用递归公用表表达式显示递归的多个级别。

以下示例显示经理以及向经理报告的雇员的层次列表。

USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM DirectReports ;
GO

E. 使用递归公用表表达式显示递归的两个级别。

以下示例显示经理以及向经理报告的雇员。将返回的级别数目被限制为两个。

F. 使用递归公用表表达式显示层次列表

以下示例在示例 C 的基础上添加经理和雇员的名称，以及他们各自的头衔。通过缩进各个级别，突出显示经理和雇员的层次结构。

USE AdventureWorks;
GO
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(varchar(255), c.FirstName + ' ' + c.LastName)
    FROM HumanResources.Employee AS e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID
    WHERE e.ManagerID IS NULL
    UNION ALL
    SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' +
                 LastName)
    FROM HumanResources.Employee as e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID
    JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports
ORDER BY Sort;
GO

G. 使用 MAXRECURSION 取消一条语句

可以使用 MAXRECURSION 来防止不合理的递归 CTE 进入无限循环。以下示例特意创建了一个无限循环，然后使用 MAXRECURSION 提示将递归级别限制为两级。

USE AdventureWorks;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte
    JOIN  HumanResources.Employee AS e
        ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);
GO

在更正代码错误之后，就不再需要 MAXRECURSION。以下示例显示了更正后的代码。

USE AdventureWorks;
GO
WITH cte (EmployeeID, ManagerID, Title)
AS
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT  e.EmployeeID, e.ManagerID, e.Title
    FROM HumanResources.Employee AS e
    JOIN cte ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte;
GO

H. 使用公用表表达式来有选择地执行 SELECT 语句中的递归操作

以下示例显示了为 ProductAssemblyID = 800 生产自行车所需的产品装配和部件层次结构。

USE AdventureWorks;
GO
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
        b.EndDate, 0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800
          AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
        bom.EndDate, ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom
        INNER JOIN Parts AS p
        ON bom.ProductAssemblyID = p.ComponentID
        AND bom.EndDate IS NULL
)
SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,
        ComponentLevel
FROM Parts AS p
    INNER JOIN Production.Product AS pr
    ON p.ComponentID = pr.ProductID
ORDER BY ComponentLevel, AssemblyID, ComponentID;
GO

I. 在 UPDATE 语句中使用递归 CTE

以下示例将直接或间接向 ManagerID 12 报告的所有雇员的 VacationHours 值增加 25%。公用表表达式将返回直接向 ManagerID 12 报告的雇员以及直接向这些雇员报告的雇员等的层次列表。只修改公用表表达式所返回的行。

USE AdventureWorks;
GO
WITH DirectReports(EmployeeID, NewVacationHours, EmployeeLevel)
AS
(SELECT e.EmployeeID, e.VacationHours, 1
  FROM HumanResources.Employee AS e
  WHERE e.ManagerID = 12
  UNION ALL
  SELECT e.EmployeeID, e.VacationHours, EmployeeLevel + 1
  FROM HumanResources.Employee as e
  JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
UPDATE HumanResources.Employee
SET VacationHours = VacationHours * 1.25
FROM HumanResources.Employee AS e
JOIN DirectReports AS d ON e.EmployeeID = d.EmployeeID;
GO

使用多个定位点和递归成员

以下示例使用多个定位点和递归成员来返回指定的人的所有祖先。创建了一个表，并在表中插入值，以建立由递归 CTE 返回的宗谱。

-- Genealogy table
IF OBJECT_ID('Person','U') IS NOT NULL DROP TABLE Person;
GO
CREATE TABLE Person(ID int, Name varchar(30), Mother int, Father int);
GO
INSERT Person VALUES(1, 'Sue', NULL, NULL);
INSERT Person VALUES(2, 'Ed', NULL, NULL);
INSERT Person VALUES(3, 'Emma', 1, 2);
INSERT Person VALUES(4, 'Jack', 1, 2);
INSERT Person VALUES(5, 'Jane', NULL, NULL);
INSERT Person VALUES(6, 'Bonnie', 5, 4);
INSERT Person VALUES(7, 'Bill', 5, 4);
GO
-- Create the recursive CTE to find all of Bonnie's ancestors.
WITH Generation (ID) AS
(
-- First anchor member returns Bonnie's mother.
    SELECT Mother
    FROM Person
    WHERE Name = 'Bonnie'
UNION
-- Second anchor member returns Bonnie's father.
    SELECT Father
    FROM Person
    WHERE Name = 'Bonnie'
UNION ALL
-- First recursive member returns male ancestors of the previous generation.
    SELECT Person.Father
    FROM Generation, Person
    WHERE Generation.ID=Person.ID
UNION ALL
-- Second recursive member returns female ancestors of the previous generation.
    SELECT Person.Mother
    FROM Generation, Person
    WHERE Generation.ID=Person.ID
)
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation, Person
WHERE Generation.ID = Person.ID;
GO

以上文字来自MSDN，方便查阅。

http://msdn2.microsoft.com/zh-cn/library/ms190766.aspx

http://msdn2.microsoft.com/zh-cn/library/ms186243.aspx

http://msdn2.microsoft.com/zh-cn/library/ms175972.aspx

刷新页面返回顶部

valens 互联网与技术