mysql - 为什么 HAVING 中的这个 COUNT 没有按预期工作？

Question

我使用以下查询来选择所有状态 ID 出现次数超过 3 次的客户。该查询还计算客户的状态 ID 出现的次数:

SELECT `bp_customer_id`, `status_id`, COUNT(`status_id`) FROM `bp_orders` 
GROUP BY `status_id`, `bp_customer_id` 
ORDER BY `bp_customer_id`  

它给出以下输出:

在上表中，客户 1000 出现了 3 次。所以我只想选择那些在表中出现 3 次或 3 次以上的客户。我们该怎么做呢？我在上面的查询中使用了 COUNT() 和 HAVING 但没有运气。

SELECT `bp_customer_id`, `status_id`, COUNT(`status_id`) FROM `bp_orders` 
GROUP BY `status_id`, `bp_customer_id` 
HAVING COUNT(`bp_customer_id`) >= 3 
ORDER BY `bp_customer_id`

我哪里做错了？

Answer 1

尚未检查这是否有效，但进行子选择可能会有所帮助

SELECT A.`bp_customer_id`, A.`status_id`, COUNT(`status_id`) FROM `bp_orders` A, {
    SELECT COUNT(*) AS customerCount, `bp_customer_id` FROM `bp_orders` GROUP BY `bp_customer_id`
} B WHERE A.`bp_customer_id` = B.`bp_customer_id`
AND B.customerCount >= 3
GROUP BY A.`status_id`, A.`bp_customer_id` 
ORDER BY A.`bp_customer_id`