我能在GROUP BY之前执行COUNT()吗？

Question

我正在为学校做一个mySQL作业，但是我卡在了一个问题上。我对mySQL还很陌生。COUNT(o.customer_id)没有按照我的意愿工作。我想要它计算订单数量，但它却计算了所有项目。例如，客户1有2个订单，但它返回的是3，因为一个订单有两个项目。我有三个表格，一个是顾客，另一个是订单，再一个是每个订单上的每个项目。我在下面放了我的查询。任何帮助都将不胜感激。

SELECT email_address, COUNT(o.order_id) AS num_of_orders,
       SUM(((item_price - discount_amount) * quantity)) AS total
FROM customers c JOIN orders o
      ON c.customer_id = o.customer_id
     JOIN order_items ot
      ON o.order_id = ot.order_id
GROUP BY o.customer_id
HAVING num_of_orders > 1
ORDER BY total DESC;

- CodyK

Answer 1

使用Distinct保留字就像是一件简单的事情：

SELECT email_address, COUNT(distinct o.order_id) AS num_of_orders

- dani herrera

Answer 2

看起来您想要计算唯一订单的数量。在COUNT中添加DISTINCT即可。虽然MySQL允许您在HAVING子句中使用SELECT表达式，但这样做不是好的实践。

SELECT email_address, COUNT(DISTINCT o.order_id) AS num_of_orders,
       SUM(((item_price - discount_amount) * quantity)) AS total
FROM customers c JOIN orders o
      ON c.customer_id = o.customer_id
     JOIN order_items ot
      ON o.order_id = ot.order_id
GROUP BY o.customer_id
HAVING COUNT(DISTINCT o.order_id) > 1
ORDER BY total DESC;

- RichardTheKiwi

---

修改这一行代码对我来说产生相同的结果。 - CodyK

抱歉！不针对MySQL...请参考此链接：http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html（查看第二个select示例）。 - dani herrera

Answer 3

只需将连接项移除即可。当存在多个项目时，它只是在复制行。

SELECT email_address, COUNT(o.order_id) AS num_of_orders,
       SUM(((item_price - discount_amount) * quantity)) AS total
FROM customers c JOIN orders o
      ON c.customer_id = o.customer_id
GROUP BY o.customer_id
HAVING COUNT(o.order_id) > 1
ORDER BY total DESC;

- Marlin Pierce