mysql - SQL查询: Showing multiple data and total order values

Question

作为 SQL 查询作业的一部分，我需要满足以下条件:

"Display all customers who have bought anything in the last 6 months. Show >customer name, loyalty card number, date of order, and total value of order. >Ensure this is named correctly in the query results as Total_Order_Value."

为此，我想出了一个已被标记为错误的脚本。我对反馈感到困惑，因为我相信我已经满足了问题标准。找到下面的脚本和反馈:

脚本

SELECT Aorder.*, Acustomer.*, AorderDetails.quantity, (AorderDetails.quantity *AmenuItem.itemCost) AS Total_Order_Value 
FROM Aorder, Acustomer, AmenuItem, AorderDetails
WHERE orderDateTime < Now() AND orderDateTime > DATE_ADD(Now(), INTERVAL -6 MONTH)
AND Acustomer.customerID = Aorder.customerID 
AND Aorder.orderID = AorderDetails.orderID
AND AorderDetails.itemID = AmenuItem.itemID
AND Aorder.paymentType IN ('Cash' , 'Card');

反馈

"At the moment this will multiply cost *qty for each individual item bought in one order. You need the total value for each order. I.e. at the moment I would see a value for each item I bought in one order, I would like to see the total for the whole order. You need to add an aggregate and a group by"

如果您能帮助我了解问题所在以及如何正确构建此结构以满足要求，我将不胜感激。预先感谢您。

Answer 1

本质上，您是在订单项级别报告结果，而不是像最初提出的问题那样在客户和订单级别报告结果。您当前的结果集可能会重复每个相应项目的客户和订单详细信息，其一对多关系可能会很长。

要解决此问题，只需将 SQL 语句重构为聚合查询，该查询对客户和订单项进行分组，并对每个订单项的值求和以检索整个订单的总金额。另外请注意 SQL 中的最佳实践:

1. EXPLICIT JOIN:正如注释中提到的，不要在 FROM 子句中使用旧的逗号连接样式，并在 WHERE 中匹配条件。这称为隐式连接。 ANSI-92 中引入的当前标准强调使用 JOIN 和 ON 子句的显式连接。虽然这不会改变 efficiency or output ，它确实有助于提高可读性和可维护性。

2. SELECT 子句:尽量避免选择带有 Aorder.*, Acustomer.* 的表中的所有字段，这是一个开放式结果集输出。您的问题专门询问了某些字段:客户姓名、成员(member)卡号、订单日期和订单总值(value)。因此，相应地选择它们。

3. 表别名:对于较长的表名以及与 A 表共享相同前缀、词干或后缀的表，请使用正确缩写和定义标识符的表别名。同样，这种做法不应改变输出，但有助于提高可读性和可维护性。

请参阅下面的工作 SQL 语句(根据实际情况调整字段名称)。

SELECT c.customer_name, 
       c.loyalty_card_number, 
       CAST(o.orderDateTime AS DATE)  AS Order_Date,                      
       SUM(d.quantity * m.itemCost) AS Total_Order_Value 

FROM Aorder o
INNER JOIN Acustomer c ON c.customerID = o.customerID 
INNER JOIN AorderDetails d ON o.orderID = d.orderID
INNER JOIN AmenuItem m ON d.itemID = m.itemID
WHERE o.orderDateTime < Now() 
  AND o.orderDateTime > DATE_ADD(Now(), INTERVAL -6 MONTH)
  AND o.paymentType IN ('Cash' , 'Card')

GROUP BY c.customer_name, 
         c.loyalty_card_number, 
         CAST(o.orderDateTime AS DATE)

注意:不要像许多 MySQL 用户那样犯错误，排除 ANSI-SQL 中要求的聚合查询的 GROUP BY 子句中的非聚合列。星号、* 不应在聚合查询中使用。不幸的是，MySQL 允许此功能，其 ONLY FULL GROUP BY模式关闭并且可能返回不可靠的结果。