SQL Server 中的通用表表达式 (CTE)

我们将使用 SQL Server 的通用表表达式 (CTE) 来简化复杂的连接和子查询。它还提供了一种查询分层数据（例如组织层次结构）的方法。本文全面概述了 CTE、CTE 的类型、优点、缺点以及如何在 SQL Server 中使用它们。

SQL Server 中的 CTE 是什么？

CTE（通用表表达式）是一个一次性结果集，只存在于查询的持续时间内。它允许我们在单个 SELECT、INSERT、UPDATE、DELETE、CREATE VIEW 或 MERGE 语句的执行范围内引用数据。它是临时的，因为它的结果不能存储在任何地方，并且在查询执行完成后立即丢失。它首次出现在 SQL Server 2005 版本中。DBA 总是更喜欢使用 CTE 作为子查询/视图的替代方案。它们遵循 ANSI SQL 99 标准并符合 SQL 规范。

SQL Server 中的 CTE 语法

CTE 语法包括 CTE 名称、一个可选的列列表以及定义通用表表达式 (CTE) 的语句/查询。定义 CTE 后，我们可以在 SELECT、INSERT、UPDATE、DELETE 和 MERGE 查询中将其用作视图。

以下是 SQL Server 中 CTE 的基本语法

在此语法中

• 我们首先指定了将在查询中稍后引用的 CTE 名称。
• 下一步是创建逗号分隔的列列表。它确保 CTE 定义参数中的列数和查询中的列数必须相同。如果我们没有定义 CTE 参数的列，它将使用定义 CTE 的查询列。
• 之后，我们将在表达式名称后使用 AS 关键字，然后定义一个 SELECT 语句，其结果集填充 CTE。
• 最后，我们将在 SELECT、INSERT、UPDATE、DELETE 和 MERGE 语句等查询中使用 CTE 名称。

在编写 CTE 查询定义时应牢记；我们不能使用以下子句

1. ORDER BY，除非您也使用 TOP 子句
2. INTO
3. 带查询提示的 OPTION 子句
4. FOR BROWSE

下图是 CTE 查询定义的表示。

这里，第一部分是一个 CTE 表达式，其中包含一个可以在 SQL 中独立运行的 SQL 查询。第二部分是使用 CTE 显示结果的查询。

示例

让我们通过各种示例了解 CTE 在 SQL Server 中是如何工作的。这里，我们将使用表“customer”进行演示。假设此表包含以下数据

在此示例中，CTE 名称为 customers_in_newyork，定义 CTE 的子查询返回三列：customer name、email 和 state。因此，CTE customers_in_newyork 将返回所有居住在纽约州的客户。

定义 CTE customers_in_newyork 后，我们在 SELECT 语句中引用了它，以获取位于纽约的客户的详细信息。

执行上述语句后，它将给出以下输出。这里，我们可以看到结果只返回了位于纽约州的客户信息。

多个 CTE

在某些情况下，我们需要创建多个 CTE 查询并将它们连接在一起以查看结果。在这种情况下，我们可以使用多个 CTE 的概念。我们需要使用逗号运算符创建多个 CTE 查询并将它们合并成一个语句。逗号运算符前面必须有 CTE 名称，以区分多个 CTE。

多个 CTE 帮助我们简化最终连接在一起的复杂查询。每个复杂的部分都有自己的 CTE，然后可以在 WITH 子句之外引用和连接。

注意：可以使用 UNION、UNION ALL、JOIN、INTERSECT 或 EXCEPT 定义多个 CTE。

以下语法更清楚地解释了它

示例

让我们了解多个 CTE 在 SQL Server 中是如何工作的。这里，我们将使用上述“customer”表进行演示。

在此示例中，我们定义了两个 CTE 名称 customers_in_newyork 和 customers_in_california。然后这些 CTE 的子查询结果集填充了 CTE。最后，我们将在一个查询中使用 CTE 名称，该查询将返回所有位于纽约和加利福尼亚州的客户。

纽约州和加利福尼亚州。

为什么我们需要 CTE？

与数据库视图和派生表一样，CTE 可以通过使复杂查询更具可读性和简单性来简化其编写和管理。我们可以通过将复杂查询分解为可在重写查询时重用的简单块来实现此特性。

以下是它的一些用例

• 当我们需要在单个查询中多次定义派生表时，它很有用。
• 当我们需要在数据库中创建视图的替代方案时，它很有用。
• 当我们需要同时在多个查询组件上多次执行相同的计算时，它很有用。
• 当我们需要使用 ROW_NUMBER()、RANK() 和 NTILE() 等排名函数时，它很有用。

以下是它的一些优点

• CTE 使代码维护更容易。
• CTE 提高了代码的可读性。
• 它提高了查询的性能。
• CTE 使得递归查询的实现变得容易。

SQL Server 中 CTE 的类型

SQL Server 将 CTE（通用表表达式）分为两大类

1. 递归 CTE
2. 非递归 CTE

递归 CTE

引用自身的通用表表达式称为递归 CTE。它的概念基于递归，递归定义为“递归过程或定义的重复应用”。当我们执行递归查询时，它会重复迭代数据集的子集。它简单地定义为调用自身的查询。在某个时刻有一个结束条件，因此它不会无限地调用自身。

递归 CTE 必须具有 UNION ALL 语句和第二个查询定义，该定义引用 CTE 本身才能是递归的。

示例

让我们了解递归 CTE 在 SQL Server 中是如何工作的。考虑以下语句，它生成前五个奇数序列：

当我们执行此递归 CTE 时，我们将看到如下输出

以下示例是更高级的递归 CTE。这里，我们将使用“jtp_employees”表进行演示，该表包含以下数据

此示例将显示员工数据的层次结构。这里，表为每个员工提供了该员工经理的引用。该引用本身就是同一表中的一个员工 ID。

此 CTE 将给出以下输出，我们可以在其中看到员工数据的层次结构

非递归 CTE

不引用自身的通用表表达式称为非递归 CTE。非递归 CTE 简单易懂，因为它不使用递归概念。根据 CTE 语法，每个 CTE 查询都将以“With”子句开头，后跟 CTE 名称和列列表，然后是带括号的 AS。

CTE 的缺点

以下是 SQL Server 中使用 CTE 的限制

• CTE 成员不能使用 Distinct、Group By、Having、Top、Joins 等关键字子句。
• CTE 只能由递归成员引用一次。
• 我们不能将表变量和 CTE 作为参数用于存储过程。
• 我们已经知道 CTE 可以代替视图使用，但 CTE 不能嵌套，而视图可以。
• 由于它只是查询或子查询的快捷方式，因此不能在另一个查询中重用。
• CTE 参数中的列数和查询中的列数必须相同。