sql - 我如何从不同的表中计数和求和 (SQL)

Question

我正在尝试编写一个执行此操作的 SQL 语句:
对于每个有订单的客户，我需要列出客户的编号、客户的订单数量、这些订单上的商品总数以及这些商品的总价格。然后我需要按客户编号对结果进行排序。
我有下面的代码。它有效，但结果不正确。对于 1 个客户，它说订单数量为 2，但只有 1 个订单，并且价格显示为 2 个订单。另一个客户有 3 个订单，但显示 4 个。我假设我缺少连接功能？
我有 3 个表，一个客户表(Customer_Name)，订单表(order_num，order_date，customer_num)，项目表(item_num，order_num，quantity，total_price)

SELECT   customer.customer_num AS "Customer Number",
         COUNT(DISTINCT orders.order_num) AS "Order Amount",
         SUM(items.quantity) AS "Quantity of Items",
         SUM(items.total_price) AS "Total Price w/o shipping"
FROM     items, orders, customer
WHERE    customer.customer_num = orders.customer_num
AND      orders.order_num = items.order_num
GROUP BY customer.customer_num
ORDER BY customer.customer_num ASC;

任何帮助都会很棒。谢谢。

Answer 1

从逻辑上讲，您的查询看起来是正确的，但在 FROM 中使用逗号写得不好条款。此外，您无需加入 customers table 。
所以，我会推荐:

SELECT o.customer_num AS "Customer Number",
       COUNT(DISTINCT o.order_num) AS "Order Amount",
       SUM(i.quantity) AS "Quantity of Items",
       SUM(i.total_price) AS "Total Price w/o shipping"
FROM orders o JOIN
     items i
     ON o.order_num = i.order_num
GROUP BY o.customer_num
ORDER BY o.customer_num ASC;

COUNT(DISTINCT)应该在计算订单方面做你想做的事。