【译】高级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...

巴特西

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

最新文章

热门文章

巴特西

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

最新文章

热门文章

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