与不使用派生表的查询相比，使用派生表的查询有什么优势？

Question

我知道如何使用派生表，但是我仍然可以真正看到使用它们的任何真正优势。

例如，在下面的文章http://techahead.wordpress.com/2007/10/01/sql-derived-
tables/中
，作者试图展示使用派生表的查询优于没有示例的查询的优点，我们要生成一个报告，以显示每个客户在1996年下的订单总数，我们希望该结果集包括所有客户，包括当年未下订单的客户和从未下过订单的客户订单（他使用Northwind数据库）。

但是，当我比较这两个查询时，我看不到使用派生表进行查询的任何优势（如果没有其他问题，使用派生表似乎不会简化我们的代码，至少在此示例中没有）：

常规查询：

SELECT C.CustomerID, C.CompanyName, COUNT(O.OrderID) AS TotalOrders
FROM Customers C LEFT OUTER JOIN Orders O ON
       C.CustomerID = O.CustomerID AND YEAR(O.OrderDate) = 1996
GROUP BY C.CustomerID, C.CompanyName

使用派生表查询：

SELECT C.CustomerID, C.CompanyName, COUNT(dOrders.OrderID) AS TotalOrders
FROM Customers C LEFT OUTER JOIN
        (SELECT * FROM Orders WHERE YEAR(Orders.OrderDate) = 1996) AS dOrders
     ON
        C.CustomerID = dOrders.CustomerID
GROUP BY C.CustomerID, C.CompanyName

也许这不是一个很好的例子，所以您能给我看一个例子，其中派生表的好处更加明显吗？

谢谢

回复GBN：

在这种情况下，如果“客户”与“产品”之间没有关系，则无法同时捕获产品和订单汇总。

您能详细说明您的意思吗？以下查询不会产生与您的查询相同的结果集：

SELECT 
     C.CustomerID, C.CompanyName,
     COUNT(O.OrderID) AS TotalOrders,
     COUNT(DISTINCT P.ProductID) AS DifferentProducts 
FROM Customers C LEFT OUTER JOIN Orders O ON
       C.CustomerID = O.CustomerID AND YEAR(O.OrderDate) = 1996
   LEFT OUTER JOIN Products P ON 
       O.somethingID = P.somethingID  
GROUP BY C.CustomerID, C.CompanyName

回复到CADE ROUX：

此外，如果使用表达式从具有大量共享中间计算的派生列派生列，则只有一组嵌套派生表或堆叠的CTE是这样做的唯一方法：

SELECT x, y, z1, z2
FROM (
    SELECT *
           ,x + y AS z1
           ,x - y AS z2
    FROM (
        SELECT x * 2 AS y
        FROM A
    ) AS A
) AS A

以下查询不会产生与上述查询相同的结果：

SELECT x, x * 2 AS y, x + x*2 AS z1, x - x*2 AS z2
FROM A

Answer 1

在您的示例中，派生表不是严格必需的。在很多情况下，您可能需要加入一个聚合或类似的聚合中，而派生表实际上是处理该问题的唯一方法：

SELECT *
FROM A
LEFT JOIN (
    SELECT x, SUM(y)
    FROM B
    GROUP BY x
) AS B
    ON B.x = A.x

此外，如果使用表达式从具有大量共享中间计算的派生列派生列，则只有一组嵌套派生表或堆叠的CTE是这样做的唯一方法：

SELECT x, y, z1, z2
FROM (
    SELECT *
           ,x + y AS z1
           ,x - y AS z2
    FROM (
        SELECT x * 2 AS y
        FROM A
    ) AS A
) AS A

就可维护性而言，使用堆叠的CTE或派生表（它们基本上是等效的），可以使代码更具可读性和可维护性，并便于剪切和粘贴的重用和重构。优化器通常可以很容易地变平。

我通常使用堆叠的CTE而不是嵌套来提高可读性（两个示例相同）：

WITH B AS (
    SELECT x, SUM(y)
    FROM B
    GROUP BY x
)
SELECT *
FROM A
LEFT JOIN B
    ON B.x = A.x

WITH A1 AS (
    SELECT x * 2 AS y
    FROM A
)
,A2 AS (
    SELECT *
           ,x + y AS z1
           ,x - y AS z2
    FROM A1
)
SELECT x, y, z1, z2
FROM A2

关于您的问题：

SELECT x, x * 2 AS y, x + x*2 AS z1, x - x*2 AS z2 
FROM A

这使x * 2代码重复3次。如果需要更改此业务规则，则必须在3个地方进行更改-
注入缺陷的方法。每当您需要仅在一个地方进行定义且一致的中间计算时，情况就会变得更加复杂。

如果可以内联SQL
Server的标量用户定义函数（或者如果它们可接受地执行），那么这不是什么大问题，您可以简单地构建UDF来堆叠结果，而优化器可以消除冗余调用。不幸的是，SQL
Server的标量UDF实现无法很好地处理大量行。