mysql - 在联接中使用 Where 子句，使用 Group by 和 Order By

Question

我正在使用 MySQL 经典模型数据库。以下查询工作正常(注意 where 子句)

select 
    customers.customerNumber as 'Customer ID',
    customers.customerName as 'Customer Name',
    count(orders.orderNumber) as 'Total Orders Placed'
from customers
left join orders on customers.customerNumber = orders.customerNumber
where customers.customerNumber > 200
group by
    customers.customerNumber
order by 
    3 asc

但是下面会导致错误。目的是仅显示结果行集中那些下了 3 个以上订单的客户。我做错了什么？

select 
    customers.customerNumber as 'Customer ID',
    customers.customerName as 'Customer Name',
    count(orders.orderNumber) as 'Total Orders Placed'
from customers
left join orders on customers.customerNumber = orders.customerNumber
where count(orders.orderNumber) > 3
group by
    customers.customerNumber
order by 
    3 asc

MySQL 错误是:错误代码:1111。组函数的使用无效

Answer 1

聚合函数(COUNT()、AVG()、SUM()、 等)不能出现在 WHERE 子句中，因为它们是在计算时出现的。相反，它们属于 HAVING 子句:

select 
    customers.customerNumber as 'Customer ID',
    customers.customerName as 'Customer Name',
    count(orders.orderNumber) as 'Total Orders Placed'
from customers
left join orders on customers.customerNumber = orders.customerNumber
group by
    customers.customerNumber
HAVING count(orders.orderNumber) > 3
order by 
    3 asc