我知道如何使用派生表，但我仍然看不到使用它们的任何真正优势。
例如，在下面的文章 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:
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
最佳答案
在您的示例中，派生表并不是绝对必要的。在许多情况下，您可能需要加入聚合或类似的数据，而派生表确实是处理该问题的唯一方法:
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个地方进行更改-注入(inject)缺陷的方法。每当您进行需要一致且仅在一处定义的中间计算时，情况就会变得复杂。
如果 SQL Server 的标量用户定义函数可以被内联(或者如果它们执行得可以接受)，这将不是什么大问题，您可以简单地构建您的 UDF 来堆叠您的结果，优化器将消除冗余调用。不幸的是，SQL Server 的标量 UDF 实现不能很好地处理大量行。