SQL中的公共表表达式（CTE）简介

公共表表达式（Common Table Expressions，简称CTE）是SQL查询中的一种强大工具，它允许定义临时的结果集，这些结果集可以在SELECT、INSERT、UPDATE或DELETE语句中被引用。CTE的概念最早在SQL Server 2005版本中引入，并符合ANSI SQL 99标准。CTE总是返回一个结果集，它们被用来简化查询，例如，可以使用CTE来消除主查询体中的派生表。

所有本教程的例子都是基于MicrosoftSQLServer Management Studio和AdventureWorks2012数据库。可以使用这些免费工具开始学习，具体可以参考指南《开始使用SQL Server》。

什么是CTE或SQLServer中的公共表表达式？

CTE定义了一个临时的结果集，可以在SELECT语句中使用它。这成为了管理复杂查询的便捷方式。公共表表达式是在WITH操作符内定义的，可以以这种方式定义一个或多个CTE。

下面是一个非常简单的CTE示例：


        WITH Employee_CTE (EmployeeNumber, Title)
        AS
        (
            SELECT NationalIDNumber, JobTitle
            FROM HumanResources.Employee
        )
        SELECT EmployeeNumber, Title
        FROM Employee_CTE

让分解一下这个示例。CTE查询定义部分是蓝色部分，它包含了一个可以在SQL中独立运行的查询。这被称为CTE查询定义：


        SELECT NationalIDNumber, JobTitle
        FROM HumanResources.Employee

当运行它时，会看到如下结果：

CTE查询定义结果：

注意，当定义CTE时，给结果集及其列命名。这样，CTE就像一个视图。结果和列的命名不同，这允许用公共表表达式封装复杂的查询逻辑。

现在回到CTE，注意WITH语句。在那里将看到名称和列被定义。这些列对应于内部查询返回的列。

从外部查询的角度来看，它“看到”的就是这个定义。它不关心CTE是如何构建的，只关心它的名称和列。

因此，CTE的结果如下：

注意列名，它们基于CTE中定义的列名。

想指出的是，可以在WITH语句中定义多个CTE。这有助于简化一些非常复杂的查询，这些查询最终被连接在一起。每个复杂的部分都可以包含在自己的CTE中，然后在WITH子句外部被引用和连接。

下面是一个使用两个CTE的示例，这是一个简单的示例，但它展示了如何定义两个CTE，然后使用它们：


        WITH PersonCTE (BusinessEntityID, FirstName, LastName)
        AS (
            SELECT Person.BusinessEntityID, FirstName, LastName
            FROM Person.Person
            WHERE LastName LIKE 'C%'
        ),
        PhoneCTE (BusinessEntityID, PhoneNumber)
        AS (
            SELECT BusinessEntityID, PhoneNumber
            FROM Person.PersonPhone
        )
        SELECT FirstName, LastName, PhoneNumber
        FROM PersonCTE
        INNER JOIN PhoneCTE
        ON PersonCTE.BusinessEntityID = PhoneCTE.BusinessEntityID;

第一个公共表表达式是绿色的，第二个是蓝色的。从SELECT语句中可以看到，CTE就像表格一样被连接。希望可以看到，随着查询变得更加复杂，CTE可以成为一个非常有用的工具来分离操作；因此，简化最终查询。

为什么需要CTE？

可能想要使用CTE而不是其他方法的原因有很多。其中一些包括：

可读性 - CTEs促进了可读性。而不是将所有的查询逻辑都塞进一个大型查询中，创建几个CTE，然后在语句中稍后组合它们。这让可以得到需要的数据块，然后在最终的SELECT中组合它们。
替代视图 - 可以用CTE替代视图。这很方便，如果没有权限创建视图对象，或者不想创建一个，因为它只在这个查询中使用。
递归 - 使用CTE创建递归查询，即可以自调用的查询。这在需要处理层次数据，如组织结构图时很有用。
限制 - 克服SELECT语句的限制，如自引用（递归）或使用非确定性函数进行GROUP BY。
排名 - 每当想要使用排名函数，如ROW_NUMBER()、RANK()、NTILE()等。

CTE的类型

公共表表达式可以分为两大类：递归CTE和非递归CTE。

递归CTE是引用它们自己的公共表表达式。递归可能是一个很难理解的话题，直到1986年上了一门LISP课程，但希望能向解释清楚。

将在另一篇文章中深入探讨递归，但现在让用这个图表向介绍递归：

在这里，看到一对相对的镜子。由于反射，它变成了一个画面中的画面。递归查询就像那样。

当递归查询运行时，它会在数据的一个子集上重复运行。递归查询基本上是一个自调用的查询。在某个点上，有一个结束条件，所以它不会无限期地自调用。

在某种程度上，当看这幅画时，可以想象每个画面中的画面是画面自调用。然而，与镜子中的“无限反射”不同，递归查询在遇到结束条件时会停止自调用。

在这一点上，递归开始解开，收集并计算数据，因为它回顾每个连续的结果。

非递归CTE，顾名思义，是不使用递归的公共表表达式。它们不引用自己。它们更容易理解，所以将在本系列的下一篇文章中首先详细查看它们。

希望现在对CTE是什么以及为什么要使用它们有了一定的了解。在接下来的两篇文章中，将更详细地讨论CTE，以及何时使用它们。

DocuPanel：将Markdown文档集成到WPF应用

本文详细介绍了如何使用DocuPanel将Markdown文档集成到WPF应用程序中，包括安装、配置和使用示例。

页面部分更新技术：联系表单示例

本文介绍了如何使用ASP.NET实现页面部分更新技术，并通过一个联系表单示例展示了异步提交数据到服务器的过程。

SQL中的公共表表达式（CTE）简介

什么是CTE或SQLServer中的公共表表达式？

为什么需要CTE？

CTE的类型

DocuPanel：将Markdown文档集成到WPF应用

页面部分更新技术：联系表单示例

沪ICP备2024098111号-1

上海秋旦网络科技中心：上海市奉贤区金大公路8218号1幢联系电话：17898875485

SQL中的公共表表达式（CTE）简介

什么是CTE或SQLServer中的公共表表达式？

为什么需要CTE？

CTE的类型

DocuPanel：将Markdown文档集成到WPF应用

页面部分更新技术：联系表单示例

沪ICP备2024098111号-1

上海秋旦网络科技中心：上海市奉贤区金大公路8218号1幢 联系电话：17898875485

上海秋旦网络科技中心：上海市奉贤区金大公路8218号1幢联系电话：17898875485