MySQL 用户<>评论关系中的MySql Select、Count(*)和SubQueries
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10022527/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySql Select, Count(*) and SubQueries in Users<>Comments relations
提问by WesternTune
I have a task to count the quantity of users having count of comments > X.
我有一个任务来计算评论数 > X 的用户数量。
My SQL-query looks like this:
我的 SQL 查询如下所示：
SELECT users.id,
users.display_name,
(SELECT COUNT(*)
FROM cms_comments
WHERE cms_comments.author_id = users.id) AS comments_count
FROM users
HAVING comments_count > 150;
Everything is ok, it shows all users correctly. But i need query to return the quantity of all these users with one row. I don't know how to change this query to make it produce correct data.
一切正常，它正确显示所有用户。但我需要查询以一行返回所有这些用户的数量。我不知道如何更改此查询以使其生成正确的数据。
回答by Mosty Mostacho
I think this is what you're looking for:
我认为这就是你要找的：
select count(*) from (
select u.id from users u
join cms_comments c on u.id = c.author_id
group by u.id
having count(*) > 150
) final
回答by Dan P
Use the group by clause
使用 group by 子句
SELECT users.id,
users.display_name,
(SELECT COUNT(*)
FROM cms_comments
WHERE cms_comments.author_id = users.id) AS comments_count
FROM users
GROUP BY users.id, user.display_name
HAVING comments_count > 150;
This will give you a count for each of the users.id, users.display_name having a commments_count > 150
这将为您提供每个 users.id、users.display_name 的计数，其中 commments_count > 150
as for your comment of getting the total number of users it's best to update your question but if you want a count of all users matching this criteria use
至于您获得用户总数的评论，最好更新您的问题，但如果您想统计符合此条件的所有用户，请使用
SELECT COUNT(*) AS TotalNumberOfUsersMatchingCritera
FROM
(
SELECT users.id,
users.display_name,
(SELECT COUNT(*)
FROM cms_comments
WHERE cms_comments.author_id = users.id) AS comments_count
FROM users
GROUP BY users.id, user.display_name
HAVING comments_count > 150;
) AS T
