SQL group by count where count 大于

Question

我有这个对组进行计数的 SQL，但我想说计数大于 1 的地方，任何人都可以提供帮助，因为它目前不起作用？

select Code, Qty, Count(Qty) from Product where ItemName = 'Banana'
and Count(Qty) > 1
Group by Code, Qty order by 3 desc

Answer 1

你应该把这个条件放在 HAVING 中-条款:

select Code, Qty, Count(Qty) Qty
from Product 
where ItemName = 'Banana'
Group by Code
having count(Qty) > 1
order by 3 desc

HAVING在 GROUP BY 之后评估而 WHERE之前被评估，这意味着 WHERE -clauses 将过滤记录级别，而 HAVING -clauses 过滤聚合。

如需更详细的解释，请查看 SQL - having VS where

我还建议您阅读 Logical Processing Order of the SELECT statement