SQLServer中的CTE(Common Table Expression)通用表表达式使用详解

概述

我们经常会编写由基本的 SELECT/FROM/WHERE 类型的语句派生而来的复杂 SQL 语句。其中一种方案是需要编写在 FROM 子句内使用派生表（也称为内联视图）的 Transact-SQL (T-SQL) 查询来使开发人员能获取一个结果集，并立即将该结果集加入到 SELECT 语句中的其他表、视图和用户定义函数中。另一种方案是使用视图而不是派生表。这两种方案都有其各自的优势和劣势。

当使用 SQL Server 2005 +时，我更倾向于第三种方案，就是使用通用表表达式 (CTE)。CTE 能改善代码的可读性（以及可维护性），且不会有损其性能。此外，与早期版本的 SQL Server 相比，它们使得用 T-SQL 编写递归代码简单了许多。

本文将介绍 CTE 的工作原理以及可用它们来应对的情况。接着将讨论使用 CTE 相对于使用传统的 T-SQL 构造的优势，如派生表、视图和自定义过程。通过事例解释它们的使用方法和适用情况。还将演示 CTE 是如何处理递归逻辑并定义递归 CTE 的运行方式的。本文使用 SQL Server2014附带的 Northwind 和 AdventureWorks 样例数据库。

视图、派生表和 CTE

如果查询需要在一组数据中进行选取，而这些数据在数据库中并不是以表的形式存在，则 CTE 可能非常有用。例如，您可能想要编写一个针对一组聚合数据的查询，该聚合数据基于客户及其订单来计算值。这些聚合数据可能会将 Customers、Orders 和 Order Details 表联接在一起，以计算订单的总和以及平均值。此外，您可能想要查询聚合的行集。一个方法是创建一个视图，首先收集聚合数据，然后针对该视图编写一个查询。另一个方法是使用派生表针对聚合数据编写一个查询 通过将 SQL 语句移到 FROM 子句中并对其进行查询，可实现这一点。

视图通常用来分解大型的查询，以便用更易读的方式来查询它们。例如，一个视图可以表示一个 SELECT 语句，该语句会将 10 个表联接起来，选择许多列，然后根据涉及的一组逻辑来过滤行。接着，可以通过其他 SELECT 语句在整个数据库中查询该视图。此抽象使由该视图表征的行集更容易访问，而且无需在临时表中复制或存储数据。

假定权限许可，这个视图还能在整个数据库中被重复使用。例如，在Figure 1 中，已经创建了一个视图，并为另一个 T-SQL 语句所使用。然而，当您想要收集数据并且只使用一次的时候，视图未必是最佳解决方案。由于视图是存在于数据库中、适用于所有批处理的数据库对象，那么创建仅用于单个 T-SQL 批处理的视图就有些多余。

Figure 1 被查询的视图

CREATE VIEW vwMyView AS
SELECT
    EmployeeID, COUNT(*) AS NumOrders,  MAX(OrderDate) AS MaxDate
FROM Orders
GROUP BY EmployeeID
GO

SELECT
    e.EmployeeID, oe.NumOrders, oe.MaxDate, e.ReportsTo AS ManagerID,
    om.NumOrders, om.MaxDate
FROM
    Employees AS e
    INNER JOIN vwMyView AS oe ON e.EmployeeID = oe.EmployeeID
    INNER JOIN vwMyView AS om ON e.ReportsTo = om.EmployeeID

另一种方法是创建派生表（也称为内联视图）。要创建派生表，在由括号包围的 FROM 子句中移动 SELECT 语句即可。接着就能像表或视图一样查询或者联接它。

Figure 2 中的代码解决的查询与Figure 1 所解决的相同，但使用的是派生表而不是视图。尽管只能在派生表所在的语句中访问它们，但是，表通常使查询变得更难以阅读和维护。如果想要在同一个批处理中多次使用派生表，此问题会变得更加严重，因为随后必须复制和粘贴派生表才能重复使用它。

Figure 2 使用派生表的查询

SELECT
    e.EmployeeID, oe.NumOrders, oe.MaxDate,  e.ReportsTo AS ManagerID,
    om.NumOrders, om.MaxDate
FROM
    Employees AS e
    INNER JOIN
        (SELECT EmployeeID, COUNT(*), MAX(OrderDate)
         FROM Orders
         GROUP BY EmployeeID) AS oe(EmployeeID, NumOrders, MaxDate)
        ON e.EmployeeID = oe.EmployeeID
    LEFT JOIN
        (SELECT EmployeeID, COUNT(*), MAX(OrderDate)
         FROM Orders
         GROUP BY EmployeeID) AS om(EmployeeID, NumOrders, MaxDate)
        ON e.ReportsTo = om.EmployeeID

CTE 非常适用于此类情形，它不仅提升了 T-SQL 的可读性（就像视图一样），而且能在同一个批处理后紧跟的查询中多次使用。当然，超出该范围它就不适用了。另外，CTE 是语言级别的构造， SQL Server 不会在内部创建临时表或虚拟表。每次在紧随其后的查询中引用 CTE 的底层查询时都会调用它。

因此，同样的情形也能用 CTE 来编写，如Figure 3 所示。EmpOrdersCTE 收集聚合数据，然后在紧随 CTE 之后的查询中使用该数据。使用 CTE 之后，Figure 3 中的代码令查询变得非常易读（就像视图一样），而且并没有创建系统对象来存储元数据。

Figure 3 使用 CTE 查询

;WITH EmpOrdersCTE (EmployeeID, NumOrders, MaxDate) AS
(
  SELECT EmployeeID, COUNT(*), MAX(OrderDate)
  FROM Orders
  GROUP BY EmployeeID
)

SELECT
    e.EmployeeID,  oe.NumOrders, oe.MaxDate,
    e.ReportsTo AS ManagerID,  om.NumOrders, om.MaxDate
FROM
    Employees AS e
    INNER JOIN EmpOrdersCTE oe ON e.EmployeeID = oe.EmployeeID
    LEFT JOIN EmpOrdersCTE om ON e.ReportsTo = om.EmployeeID

　　

CTE 的结构

CTE语法

[ 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 的任何引用都会使用公用表表达式，而不使用基对象。

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

CTE_query_definition
指定一个其结果集填充公用表表达式的 SELECT 语句。 除了 CTE 不能定义另一个 CTE 以外，CTE_query_definition 的 SELECT 语句必须满足与创建视图相同的要求。

如果定义了多个 CTE_query_definition，则这些查询定义必须用下列一个集合运算符联接起来：UNION ALL、UNION、EXCEPT 或 INTERSECT。

现在我将用一个简单的 CTE 为例来演示如何构造 CTE。CTE 以 WITH 关键字开始。然而，如果 CTE 不是批处理中的第一个语句，则必须在 WITH 关键字前添加一个分号。作为最佳做法，我倾向于在所有的 CTE 之前都加上一个分号作为前缀，我发现这种一致的方式比起必须牢记是否需要添加分号来，要容易得多。

WITH 关键字后面是 CTE 的名称，接着是一个列别名的可选列表。列别名对应于 CTE 内的 SELECT 语句返回的列。可选列别名的后面是 AS 关键字，这是必需的。AS 关键字后面是用括号括起来、定义 CTE 的查询表达式。

请看这个示例：

;WITH myCTE (CustID, Co) AS
(
  SELECT CustomerID, CompanyName FROM Customers
)
SELECT CustID, Co FROM myCTE

CustomerID 和 CompanyName 列的别名为 CustID 和 Co。接着跟随 CTE 其后的是通过列别名引用 CTE 的 SELECT 语句。

理解 CTE

在设计 CTE 之前，必须理解它的工作原理和遵循的规则。本文介绍了 CTE 的适用情况，以及在 CTE 内什么是可以使用的，什么是不可以使用的。对于初学者来说，可以在 T-SQL 批处理、用户自定义函数、存储过程、触发器或视图中创建并使用 CTE。

CTE 仅能被紧随其后的语句所引用。这意味着如果要使用 CTE，则必须紧随 T-SQL 批处理中的 CTE 之后编写引用 CTE 的查询。例如，以下批处理会产生错误：

;WITH myCTE (CustID, Co) AS
(
  SELECT CustomerID, CompanyName FROM Customers
)
SELECT CompanyName FROM Customers WHERE CustomerID = 'ALFKI'
SELECT CustID, Co FROM myCTE

在本段代码中，myCTE 仅供紧随其后的第一个查询使用。当第二个查询引用 myCTE 时，CTE 不在范围之内，并且引发异常（对象名“myCTE”无效）。

另请注意，因为 CTE 预期由另一个可能随之肯定要重新处理数据的查询引用，所以 CTE 的查询不能含有 ORDER 和 COMPUTE 之类的语句。然而，诸如 FOR XML 的复杂语句仍可用来定义和运行 CTE。例如，您可以使用 FOR XML 子句来查询 CTE 并返回其结果，如下所示。

;WITH myCTE AS
(
  SELECT c.CustomerID, c.CompanyName, o.OrderID, o.OrderDate
  FROM Customers c
  INNER JOIN Orders o ON c.CustomerID = o.CustomerID
)

SELECT CustomerID, CompanyName, OrderID, OrderDate
FROM myCTE FOR XML AUTO

一旦定义了 CTE，跟随其后的首个查询便能多次引用它。这一功能在某个查询需要多次引用 CTE 时尤为有用。Figure 3 中的代码示例演示了查询如何引用 EmpOrdersCTE 两次，以便能获取员工和主管的信息。当需要多次引用同一行集时，这非常有用；引用 CTE 两次比复制该查询要简单得多。

CTE 并不一定由 SELECT 语句使用；任何引用 CTE 所生成行集的语句都可使用它。这意味着 CTE 后面可以跟随使用 CTE 的 SELECT、INSERT、UPDATE 或 DELETE 语句。您也可以在使用 CTE 的查询中使用只进和快照光标。

此外，CTE 后面也可以跟随另一个 CTE。在想要把中间结果聚集到行集时，可使用这种技术从其他 CTE 构建 CTE。当创建从其他 CTE 构建的 CTE 时，请用逗号分隔 CTE 的定义。

Figure 4 所示的示例定义了 EmpOrdersCTE，它收集了一个员工列表和每个员工的订单总数。第二个 CTE 的名称是 MinMaxOrdersCTE，它查询第一个 EmpOrdersCTE 并在行集上执行聚合函数来确定员工订单的平均数、最小数和最大数。

Figure 4 引用另一个 CTE 的 CTE

;WITH 
EmpOrdersCTE (EmployeeID, NumOrders)
AS
(
  SELECT EmployeeID, COUNT(*)
  FROM Orders
  GROUP BY EmployeeID
),
MinMaxOrdersCTE (Mn, Mx, Diff)
AS
(
  SELECT MIN(NumOrders), MAX(NumOrders), AVG(NumOrders)
  FROM EmpOrdersCTE
)
SELECT Mn, Mx, Diff
FROM MinMaxOrdersCTE

通过用逗号来分隔，在 WITH 关键字后面可以定义多个 CTE。每个 CTE 都可以被紧随其后的 CTE 所引用，形成层接的构建关系。CTE 定义后面的数据操作语言 (DML) 语句也可引用 WITH 子句中定义的任何 CTE。

递归规则

CTE 还可用于实现递归算法。在需要编写调用其本身的算法时，递归逻辑很有用——这通常用来遍历一组嵌套的数据。编写递归逻辑可能很复杂，特别是使用 T-SQL 之类的语言的时候。然而，这正是 CTE 旨在解决的特别问题之一。创建递归 CTE 的基本公式如下所示：

  1. 创建一个返回顶层（这是定位点成员）的查询。
  2. 编写一个递归查询（这是递归成员）。
  3. 通过 UNION 将第一个查询与递归查询结合起来。
  4. 确保存在没有行会被返回的情况（这是终止检查）。

您的递归 CTE 如下所示：

;WITH myRecursiveCTE(col1, col2, ... coln) AS
( 
  -- 定位点成员 查询
  UNION ALL
  -- 递归成员 查询来自CTE的自身数据
)

　　当编写不涉及 CTE 的自定义递归过程时，必须包含一个显式终止子句。这个终止子句负责确保递归算法最后将终止，并弹出了递归调用堆栈。若无此子句，您的代码最终将无限循环下去。

　　CTE 可从两个方面帮助处理终止子句。首先是一个隐式终止子句，当递归成员返回零记录时出现。此时，递归成员查询不会递归调用 CTE，取而代之的弹出了递归调用堆栈。其次是能显式设置 MAXRECURSION 层。

MAXRECURSION 层可以在含有 CTE 的批处理中或通过服务器端设置（服务器范围的设置默认值为 100，除非您更改它）显式设置。这个设置限制了 CTE 可递归调用其本身的次数。如果达到限制次数，则将引发异常。设置 MAXRECURSION 层的语法是在 SELECT 语句中的 CTE 后面使用 OPTION 子句，如下所示：

-- DEFINE YOUR CTE HERE
SELECT * FROM EmpCTE
OPTION (MAXRECURSION 7)

在设计递归 CTE 时，还有一些其他规则也得铭记在心。递归 CTE 必须包含定位点成员和递归成员。这两种成员必须拥有相同数量的列，而且同属于这两种成员的列必须具有匹配的数据类型。递归成员只能引用 CTE 一次，并且成员不能使用下列子句或关键字：

  • SELECT DISTINCT
  • GROUP BY
  • HAVING
  • TOP
  • LEFT/RIGHT OUTER JOIN

递归偏移

在数据和行集方面，递归用于解决您需要在不同情况下针对同一组数据重复执行同一逻辑时所遇到的问题。例如，假设您需要找出所有销售人员以及他们的上级，然后以分层顺序返回数据。Figure 5 演示了一个使用 CTE 的解决方案，该方案通过递归来收集销售副总裁下属的员工的列表。

Figure 5 递归收集销售人员

;WITH EmpCTE(EmployeeID, EmployeeFirstName,EmployeeLastName, MgrID, SalesLevel)
AS
(
  -- 定位点成员
  SELECT EmployeeID, FirstName, LastName, ReportsTo, 0
  FROM Employees
  WHERE EmployeeID = 2 -- Start with the VP of Sales

  UNION ALL

  -- 递归成员
  SELECT
    e.EmployeeID, e.FirstName, e.LastName, e.ReportsTo, m.SalesLevel+1
  FROM
    Employees AS e
    INNER JOIN EmpCTE m ON e.ReportsTo = m.EmployeeID
)

 -- 使用CTE
SELECT EmployeeID, EmployeeFirstName, EmployeeLastName,
       MgrID, SalesLevel
FROM EmpCTE

除了少数其他方面，Figure 5 中所示的递归 CTE 看起来很像标准 CTE。其中标准 CTE 包含一个定义行集的查询，递归 CTE 定义两个查询定义。第一个查询定义（即定位点成员）定义了将在调用 CTE 时执行的查询。第二个查询定义，即递归成员，定义了一个返回与定位点成员相同的列和数据类型的查询。递归成员还检索接下来将被用于递归回调到 CTE 的值。查询的结果通过 UNION 语句结合在一起。

Figure 5 中的 EmpCTE 显示了收集销售副总裁的员工记录的定位点成员 (EmployeeID = 2)。定位点成员查询的最后一列返回 0 值，这表示分层顺序的第 0 层，也就是最顶层。递归成员查询收集向其各自上级汇报的员工的列表。这通过联接 Employees 表和 EmpCTE 来实现。

从递归成员中可以检索相同的列，但是 SalesLevel 列的计算方式是：取当前员工的主管，收集主管的 SalesLevel，然后在其基础上增加 1。表达式 m.SalesLevel+1 为所有直接向销售副总裁汇报的员工（从定位点成员中检索到）的 SalesLevel 赋值 1。然后，所有向那些员工汇报的员工的 SalesLevel 值变为 2。对随后销售机构层次结构中的每一后续层次，其 SalesLevel 都以此方式递增。

补充：

目前仅有Sql Server 2005、2008、2012+、MySQL8.0+、Oracle11g、IBM DB2和PostegreSQL8.4支持CTE；SQLite和Infomix暂时不支持。

非递归公用表表达式适用准则

  • 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 中使用以下子句：
    • ORDER BY（除非指定了 TOP 子句）
    • INTO
    • 带有查询提示的 OPTION 子句
    • FOR BROWSE
  • 如果将 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
    • PIVOT（当数据库兼容性级别为 110 或更高级别时。 请参阅 SQL Server 2016 中数据库引擎功能的重大更改。）
    • HAVING
    • 标量聚合
    • TOP
    • LEFT、RIGHT、OUTER JOIN（允许使用 INNER JOIN）
    • 子查询
    • 应用于 CTE_query_definition 中 CTE 的递归引用的提示。

结束语

比起那些在查询中使用复杂的派生表或引用那些在 T-SQL 批处理外部定义的视图的方案，CTE 使得编写 T-SQL 更具可读性。此外，CTE 还为解决使用递归算法的过程中遇到的难题提供了一个更先进的工具。不管您使用的是非递归 CTE 还是递归 CTE，您都会发现 CTE 可以帮您应对许多常见开发情况，并且可在不损害性能的情况下提升可读性。

更多使用详细介绍与使用可参考：https://docs.microsoft.com/zh-cn/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15

posted @ 2022-01-04 14:56  .NET快速开发框架  阅读(787)  评论(1)  编辑  收藏  举报