【译】高级T-SQL进阶系列 （三）【中篇】：理解公共表表达式（CTEs）

【译注：此文为翻译，由于本人水平所限，疏漏在所难免，欢迎探讨指正】

原文链接：传送门。

一个简单的CTE例子

如前所述，CTE‘s提供给你了一个方法来更容易的书写复杂的代码以提高其可读性。假设你有列表1所示的代码：

USE AdventureWorks2012;
GO
SELECT YearMonth, ProductID, SumLineTotal 
FROM (
    SELECT CONVERT(CHAR(7),ModifiedDate,120) AS YearMonth
         , ProductID
         , SUM(LineTotal) AS SumLineTotal
    FROM Sales.SalesOrderDetail
    GROUP BY ProductId, CONVERT(CHAR(7),ModifiedDate,120)
    ) MonthlyProductSales
WHERE YearMonth = '2008-06';

 列表1：带有子查询的代码

列表1具有一个SELECT语句，其带有一个在FROM子句中的子查询。这个子查询是一个具有别名MonthlyProductSales 的派生表，其对ModifiedDate字段的每一个Year/Month组合都总结了其LineTotal 数量。从我的MonthlyProductSales 子查询的结果中，我对结果进行约束，使得其仅仅返回那些年和月为 2008，06的数据行。

虽然列表1的代码已经是相当的简洁了，我们可以通过用一个CTE来重写它以提高它的可读性，就像我在列表2中所做的那样。

USE AdventureWorks2012;
GO
-- CTE Definition
WITH MonthlyProductSales AS
(
SELECT CONVERT(CHAR(7),ModifiedDate,120) AS YearMonth
, ProductID
, SUM(LineTotal) AS SumLineTotal
FROM Sales.SalesOrderDetail
GROUP BY ProductId, CONVERT(CHAR(7),ModifiedDate,120)
)
-- SELECT statement referencing CTE
SELECT * FROM MonthlyProductSales
WHERE YearMonth = '2008-06';

 列表2：使用CTE对列表1的代码进行重写

在列表2中我将列表1中的派生表子查询移到了一个名为MonthlyProductSales的CTE中。接下来我将SELECT语句中的子查询用CTE的名字来替换，在这个示例中，是MonthlyProductSales。通过移动列表1中的子查询到一个CTE的定义中，很多人都会发现列表2中的代码更容易阅读和维护。

使用多个CTE’s 的示例

我在列表1中的代码并不是那么复杂。如果你的代码比较复杂并且包含多个子查询，那么你应该考虑重写它，从而使它更易维护且更具有可读性。而重写的方法之一便是移除子查询并用CTE来对其进行重写。

为了演示在一个SELECT语句中使用多个CTE‘s，假设我有列表3所示的非CTE的查询：

USE AdventureWorks2012;
GO
SELECT SalesPersonID
  , SalesYear
  , TotalSales
  , SalesQuotaYear
  , SalesQuota
FROM ( -- First Subquery
       SELECT SalesPersonID
            , SUM(TotalDue) AS TotalSales
            , YEAR(OrderDate) AS SalesYear
       FROM Sales.SalesOrderHeader
       WHERE SalesPersonID IS NOT NULL
       GROUP BY SalesPersonID, YEAR(OrderDate)
    ) AS Sales 
JOIN ( -- Second Subquery
       SELECT BusinessEntityID
            , SUM(SalesQuota)AS SalesQuota
            , YEAR(QuotaDate) AS SalesQuotaYear
       FROM Sales.SalesPersonQuotaHistory
       GROUP BY BusinessEntityID, YEAR(QuotaDate)
    ) AS Sales_Quota 
ON Sales_Quota.BusinessEntityID = Sales.SalesPersonID
AND Sales_Quota.SalesQuotaYear = Sales.SalesYear   
ORDER BY SalesPersonID, SalesYear;

  列表3：在一个单独的SELECT语句中使用多个子查询

列表3中的查询包含两个不同的子查询，它们都实现为一个派生表。第一个子查询通过SalesPersonID，SalesYear来计算TotalSales。基于BusinessEntityID 和SalesQuotaYear的聚合值SalesQuota在第二个子查询中被计算。

为了简化阅读列表3中的代码，我们可以将子查询重写为两个不同的CTEs，然后在一个简单的SELECT子句中引用它们，如同我在列表4中所完成的那样：

USE AdventureWorks2012;
GO
-- First Subquery rewritten as CTE
WITH Sales AS
(
    SELECT SalesPersonID
         , SUM(TotalDue) AS TotalSales
         , YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
    GROUP BY SalesPersonID, YEAR(OrderDate)
),
-- Second Subquery rewritten as CTE
Sales_Quota AS
( 
    SELECT BusinessEntityID
         , SUM(SalesQuota)AS SalesQuota
         , YEAR(QuotaDate) AS SalesQuotaYear
    FROM Sales.SalesPersonQuotaHistory
    GROUP BY BusinessEntityID, YEAR(QuotaDate)
)  
-- SELECT using multiple CTEs
SELECT SalesPersonID
     , SalesYear
     , TotalSales
     , SalesQuotaYear
     , SalesQuota
FROM Sales
  JOIN Sales_Quota 
    ON Sales_Quota.BusinessEntityID = Sales.SalesPersonID
        AND Sales_Quota.SalesQuotaYear = Sales.SalesYear  
ORDER BY SalesPersonID, SalesYear;

 列表4：使用两个CTE’s来代替子查询

在列表4中我将列表3中的两个子查询移动到两个不同的CTE‘s中。第一个CTE，以名字Sales来进行定义，包含了通过SalesPersonID和SalesYear来产生TotalSales数量的子查询。通过在第一个CTE的后面加上一个逗号，我定义了第二个名为SalesQuota的CTE。第二个CTE包含了子查询以计算其SalesQuota数量。在定义了两个CTE之后我便可以在我最终的SELECT语句中引用它们。

能够在一个单独的WITH子句中定义多个CTE’s，并在最终的T-SQL语句中引用它们，这些特性允许我将我在列表3中的复杂的SQL变得更易阅读，部署以及调试。对于复杂的T-SQL逻辑使用CTE‘s允许你将你的代码分解为可维护的逻辑块或者部分。

To be continued...