mysql 计数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7857538/
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 count group by having
提问by Michael Liao
I have this table:
我有这张桌子：
Movies (ID, Genre)
A movie can have multiple genres, so an ID is not specific to a genre, it is a many to many relationship. I want a query to find the total number of movies which have at exactly 4 genres. The current query I have is
一部电影可以有多个流派，因此 ID 不是特定于流派的，它是多对多的关系。我想要一个查询来查找恰好有 4 种类型的电影总数。我目前的查询是
SELECT COUNT(*)
FROM Movies
GROUP BY ID
HAVING COUNT(Genre) = 4
However, this returns me a list of 4's instead of the total sum. How do I get the sum total sum instead of a list of count(*)?
但是，这会返回一个 4 的列表而不是总和。我如何获得总和而不是一个列表count(*)？
回答by Marc B
One way would be to use a nested query:
一种方法是使用嵌套查询：
SELECT count(*)
FROM (
SELECT COUNT(Genre) AS count
FROM movies
GROUP BY ID
HAVING (count = 4)
) AS x
The inner query gets all the movies that have exactly 4 genres, then outer query counts how many rows the inner query returned.
内部查询获取恰好有 4 种类型的所有电影，然后外部查询计算内部查询返回的行数。
回答by Conrad Frix
SELECT COUNT(*)
FROM (SELECT COUNT(*)
FROM movies
GROUP BY id
HAVING COUNT(genre) = 4) t
回答by Michael Durrant
Maybe
也许
SELECT count(*) FROM (
SELECT COUNT(*) FROM Movies GROUP BY ID HAVING count(Genre) = 4
) AS the_count_total
although that would not be the sum of all the movies, just how many have 4 genre's.
虽然这不是所有电影的总和，但有多少电影有 4 种类型。
So maybe you want
所以也许你想要
SELECT sum(
SELECT COUNT(*) FROM Movies GROUP BY ID having Count(Genre) = 4
) as the_sum_total
回答by imm
What about:
关于什么：
SELECT COUNT(*) FROM (SELECT ID FROM Movies GROUP BY ID HAVING COUNT(Genre)=4) a
