来自别名表的 MySQL Sum

Question

我有这个问题...

CREATE VIEW CustOrderItems AS
SELECT CustFirstName, CustLastName, OrderNumber, OrderDate, ShipDate, 
QuantityOrdered * QuotedPrice AS ItemTotal
FROM Customers NATURAL JOIN Orders NATURAL JOIN Order_Details;

像这样对订单进行分组后...

SELECT CustFirstName, CustLastName, OrderNumber, OrderDate, ShipDate, 
QuantityOrdered * QuotedPrice AS ItemTotal
FROM Customers NATURAL JOIN Orders NATURAL JOIN Order_Details
GROUP BY OrderNumber
ORDER BY OrderNumber ASC;

我知道需要计算所有 ItemTotal 的总和吗？

非常感谢任何帮助!

Answer 1

正如已经在评论中提到的那样，您可以使用 SUM 在 SQL 中获得总和，这不足为奇。您知道 GROUP BY，但不知道 SUM，这似乎很奇怪。

在这一点上，我想建议总是在加入之前聚合，而不是之后。您想要带有客户信息和总价的订单。所以从订单中选择，加入客户，加入总价。一旦您想要来自不同表的聚合，这种方法将为您省去一些麻烦。

SELECT 
  c.CustFirstName,
  c.CustLastName,
  o.OrderNumber,
  o.OrderDate,
  o.ShipDate, 
  od.OrderTotal
FROM Orders o
JOIN Customers c ON c.CustomerNumber = o.CustomerNumber
JOIN 
(
  SELECT
    OrderNumber,
    SUM(QuantityOrdered * QuotedPrice) AS OrderTotal
  FROM Order_Details
  GROUP BY OrderNumber
) od ON od.OrderNumber = o.OrderNumber
ORDER BY o.OrderNumber ASC;