MYSQL 过滤另一个表的计数

Question

我开始使用 MYSQL。我有以下查询:

SELECT answers.data. * , COUNT( answers.reports.id ) AS rcount
FROM answers.data
LEFT JOIN answers.reports ON answers.data.id = answers.reports.answer_id
WHERE answers.data.question='4'
GROUP BY answers.data.id

它通过添加名为 rcount(报告数量)的列返回当前问题的所有答案

我希望它只返回少于 3 个报告的那些。我尝试了以下代码:

SELECT answers.data. * , COUNT( answers.reports.id ) AS rcount
FROM answers.data
LEFT JOIN answers.reports ON answers.data.id = answers.reports.answer_id
WHERE answers.data.question='4' AND rcount < '3'
GROUP BY answers.data.id

但显然这不是新条件发生的地方。我在手册中找不到解决方案，因为我是新手。谢谢

Answer 1

使用HAVING子句:

SELECT answers.data.* , COUNT( answers.reports.id ) AS rcount
FROM answers.data
LEFT JOIN answers.reports ON answers.data.id = answers.reports.answer_id
WHERE answers.data.question='4'
GROUP BY answers.data.id
HAVING COUNT( answers.reports.id ) < 3