SQL 中的 CTE

公共表表达式 (CTE) 是一个临时结果集，可以在 SQL 语句中引用。CTE (Common Table Expressions) 出现在 SQL Server 2005 中，通过在查询期间虚拟引入新表来简化复杂的查询。它们对于使用递归查询和提高代码可读性非常强大。

要定义 CTE，**您使用 WITH 子句，后跟一个由逗号分隔的 CTE 列表**。CTE 由名称和旨在确定结果集的查询组成。CTE 可以与 SELECT、INSERT、UPDATE 和 MERGE 语句以及视图一起使用。

CTE 对于递归查询尤其有用，在这些查询中，一个查询会引用自身来处理分层数据。例如，可以通过递归 CTE 来查看公司内的员工排名，以遍历组织结构。

定义和使用递归 CTE 的规则包括需要至少两个 CTE 查询定义

锚成员必须出现在递归成员之前，并且必须与 UNION ALL、UNION、INTERSECT 或 EXCEPT 等集合运算符结合使用。与锚成员一样，在递归成员的列中，数据类型必须与锚成员的数据类型匹配。

递归 CTE 最常见的应用示例之一是分层数据，其中包括具有经理引用的员工。递归 CTE 可以通过在 CTE 中引用自身来显示完整的员工数据层次结构。可以通过将 MAXRECURSION 提示添加到主 SELECT、INSERT、UPDATE、DELETE 或 MERGE 语句来定义层次结构中每个节点的最高级别数。

示例

举个例子，考虑一个包含 EmployeeID、FirstName、LastName 和 ManagerID 列的员工表。使用递归 CTE，我们可以生成一个报告，显示员工的层次结构

说明

公共表表达式 (CTE) 定义

• 需要注意的是，该项目称为 cteReports。
• 它定义了五列： EmpID、FirstName、LastName、MgrID 和 GodLevel。
• CTE 的初始部分选择 ManagerID 为 NULL 的顶层员工。他们是部门或整个个人的负责人。
• 所选人员被赋予从第 1 个（coeffEmpLevel）开始的缩放系数。
• CTE 的递归部分将 Employees 表与 CTE 本身连接起来。它还将雇用与 CTE 基础中确定的经理密切合作的员工。因此，算法递归地进行，直到达到最后一个级别。

使用 CTE 的 SELECT 语句

• SELECT 语句从 CTE cteReports 中检索数据。
• 它通过连接 FirstName 列和 LastName 列中的数据来构建每个员工的全名。
• EmpLevel 字段描述了相应员工的层次结构程度，即他们在组织金字塔中的位置。
• 使用子查询来获取每个员工的经理名称 (Manager)。该语句是子查询，它通过 CTE 中的 MgrID 列表列从 Employees 表中检索经理姓名。
• 最后，通过 EmpLevel 和 MgrID 明确结果集，以实现对层次结构的排序。

应用

SQL 中的公共表表达式 (CTE) 在各种场景中提供了通用的解决方案

1. 递归查询： CTE 向量允许递归查询，非常适合用于分层数据结构，如组织图、物料清单或文件系统。
2. 数据转换： 它们简化了复杂的数据转换，如数据透视或反透视，使重塑数据集以进行报告或分析更加容易。
3. 代码可重用性： CTE 是一种在整个查询中拥有子查询的方法，这样我们就无需一遍又一遍地编写代码，而是应用 CTE 使查询更易于维护。
4. 递归数学运算： CTE 可以轻松解决周期性方程，例如涉及通过彻底分析计算阶乘或斐波那契数列的方程。CTE 将提供优雅的解决方案。

结论

总之，SQL 服务器中的大多数可重用性都节省了查询，尤其是对于处理动态数据结构的查询，有助于创建可读且适应性强的查询。它们为 SQL 语句中的引用提供了一个临时结果集，对于组织图等分层数据非常有用。另一方面，用户应考虑他们将获得的优点和缺点，特别是像 Azure Synapse Analytics 这样的云数据库的特性和限制。