MYSQL按另一个表的计数过滤

发布于 2024-11-25 10:18:59 字数 653 浏览 1 评论 0原文

我从 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

但显然这不是新条件所在的位置。我在手册中找不到解决方案，因为我对此很陌生。谢谢

原文

I'm beggining with MYSQL.
I have the following query:

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

It's returning me all answers to the current question with an added column called rcount (number of reports)

I want it to return only those with less than 3 reports. I tried the following code:

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

but aprrently that's not where the new condition goes. I can't find in the manual a solution since I'm new to this.
Thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我三岁 2024-12-02 10:18:59

使用 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

Use a HAVING clause:

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

回复收藏 0 原文

~没有更多了~