SQLZoo难题#12 AdventureWorks评估

Question

我是数据库领域的新手，我刚刚开始学习，发现SQLZoo网站非常有用。我在AdventureWorks数据库中遇到了这个问题。

http://sqlzoo.net/wiki/AdventureWorks_hard_questions

12.对于每个订单，显示SalesOrderID和SubTotal以三种方式计算: A)从SalesOrderHeader计算B)订单数量*单价C)订单数量*列表价格总和

由于某种原因，我想不出这一点。任何帮助都是非常感谢的。

这是我的问题：

SELECT 
   A.SalesOrderID,
   A.SubTotal AS SubTotalA,
   B.SubtotalB,
   C.SubtotalC 
FROM SalesOrderHeader A
JOIN
(
   SELECT SUM(sodB.OrderQty*sodB.UnitPrice) AS SubTotalB 
   FROM SalesOrderDetail sodB 
   JOIN ProductAW pawB 
   ON pawB.ProductID=sodB.ProductID
   GROUP BY sodB.SalesOrderID
)  AS B
JOIN
(
   SELECT SUM(sodC.OrderQty*pawC.ListPrice) AS SubTotalC 
   FROM SalesOrderDetail sodC 
   JOIN ProductAW pawC 
   ON pawC.ProductID=sodC.ProductID
   GROUP BY sodC.SalesOrderID
 )  AS C

由于某些原因，当我在JOIN外单独运行SELECT语句B或C时，我得到了预期的结果，但当我将then放在JOIN内时，SubTotalB或Subtotal C的所有值都会重复显示与第一行值相同的结果。

SELECT SUM(sodC.OrderQty*pawC.ListPrice) AS SubTotalC 
   FROM SalesOrderDetail sodC 
   JOIN ProductAW pawC 
   ON pawC.ProductID=sodC.ProductID
   GROUP BY sodC.SalesOrderID

或

SELECT SUM(sodC.OrderQty*pawC.ListPrice) AS SubTotalC 
   FROM SalesOrderDetail sodC 
   JOIN ProductAW pawC 
   ON pawC.ProductID=sodC.ProductID
   GROUP BY sodC.SalesOrderID

Answer 1

这是最终的解决方案：

SELECT 
   A.SalesOrderID,
   A.SubTotal AS SubTotalA,
   B.SubtotalB,
   C.SubtotalC 
FROM SalesOrderHeader A
JOIN
(
   SELECT SUM(sodB.OrderQty*sodB.UnitPrice) AS SubTotalB, sodB.SalesOrderID 
   FROM SalesOrderDetail sodB 
   JOIN ProductAW pawB 
   ON pawB.ProductID=sodB.ProductID
   GROUP BY sodB.SalesOrderID
)  AS B
ON A.SalesOrderID = B.SalesOrderID
JOIN
(
  SELECT SUM(sodC.OrderQty*pawC.ListPrice) AS SubTotalC, sodC.SalesOrderID 
  FROM SalesOrderDetail sodC 
  JOIN ProductAW pawC 
  ON pawC.ProductID=sodC.ProductID
  GROUP BY sodC.SalesOrderID
)  AS C
ON A.SalesOrderID = C.SalesOrderID

Answer 2

您的外部查询没有任何ON子句。这将导致表B中的每条记录都针对表A中的每条记录进行设置。因此，如果表A有5条记录，而表B有5条记录，那么总共有25条记录。当然，所有这些记录也会与表C相乘。所有AB记录将针对表C中的每条记录进行设置。

我将一个ProductID添加到每个子查询SELECT语句中，然后将一个ON语句添加到外部查询中的每个连接，以便在匹配的ProductID上进行连接。我希望这就是你想要的。

SELECT 
   A.SalesOrderID,
   A.SubTotal AS SubTotalA,
   B.SubtotalB,
   C.SubtotalC 
FROM SalesOrderHeader A
JOIN
(
   SELECT SUM(sodB.OrderQty*sodB.UnitPrice) AS SubTotalB,
   pawB.ProductID
   FROM SalesOrderDetail sodB 
   JOIN ProductAW pawB 
   ON pawB.ProductID=sodB.ProductID
   GROUP BY sodB.SalesOrderID
)  AS B 
ON A.ProductID = B.ProductID
JOIN
(
   SELECT SUM(sodC.OrderQty*pawC.ListPrice) AS SubTotalC,
   pawC.ProductID
   FROM SalesOrderDetail sodC 
   JOIN ProductAW pawC 
   ON pawC.ProductID=sodC.ProductID
   GROUP BY sodC.SalesOrderID
 )  AS C
ON A.ProductID = C.ProductID

Answer 3

我喜欢这个网站，但我希望有一个地方可以比较答案，因为我只想知道我的查询是不是以最好的方式完成的。这是我为同样的问题想出的方法：

SELECT soh.salesorderid, soh.subtotal AS "SubTotal A", SUM(sod.orderqty * sod.unitprice) AS "SubTotal B", SUM(sod.orderqty * paw.ListPrice) AS "SubTotal C" FROM SalesOrderHeader soh JOIN SalesOrderDetail sod (ON sod.salesorderid = soh.salesorderid) JOIN ProductAW paw (ON paw.productid = sod.productid) GROUP BY soh.salesorderid

我也在学习，所以如果有人想加入进来，让我知道我如何改进它，请一定要这样做。