MySQL，按特定外键的出现次数过滤

Question

我试图发现表中所有行出现不止一次的外键，并通过子查询返回这些外键。

在列上使用 GROUP BY 我能够可视化每个外键的 COUNT:

SELECT orders_id, COUNT(*) as count
FROM order_history
GROUP BY orders_id

但是当我用 WHERE 子句扩充它以尝试过滤时，如下所示:

SELECT orders_id, COUNT(*) as count
FROM order_history
GROUP BY orders_id
WHERE count > 1

我得到一个错误

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE count > 1 LIMIT 0, 30' at line 4

显然 count 在 WHERE 被评估时没有资格进行比较？

:发布答案更新:

感谢您的快速回复! HAVING 正是我所缺少的。我发现这个问题有助于扩展主题:WHERE vs HAVING

Answer 1

要对 COUNT()、SUM()、MAX()、MIN() 等聚合值应用过滤，请使用 HAVING 子句而不是 哪里。

SELECT orders_id, COUNT(*) as count
FROM order_history
GROUP BY orders_id
HAVING count > 1

语法错误是由于 WHERE 子句必须位于 GROUP BY 子句之前。然而，即使它位于正确的位置，它仍然会出错，因为 count 别名在计算 WHERE 子句时是未知的。

来自MySQL SELECT syntax documentation :

A HAVING clause can refer to any column or alias named in a select_expr in the SELECT list or in outer subqueries, and to aggregate functions.