MYSQL count、group by和where子句

Question

这是可行的

SELECT COUNT(*) AS count ,transaction_id FROM loans_applicant  GROUP BY transaction_id

然而，我想这样做

SELECT COUNT(*) AS count ,transaction_id FROM loans_applicant  GROUP BY transaction_id WHERE count > 4

我该怎么做呢？

Answer 1

该条件应包含在HAVING子句中，如下所示。此外，您将WHERE放在了错误的位置；它应该紧跟在FROM子句之后。

SELECT COUNT(*) AS count ,
transaction_id 
FROM loans_applicant  
GROUP BY transaction_id 
HAVING COUNT(*) > 4;

Answer 2

请按照以下顺序进行操作：

where
group by
having
order by

这样，您的查询将是-

SELECT COUNT(*) AS count ,transaction_id 
FROM loans_applicant 
GROUP BY transaction_id
having count > 4;