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 J