mysql 计数组

2021-12-26 00:00:00 sql group-by mysql aggregate-functions

我有这张桌子:

Movies (ID, Genre)

一部电影可以有多种类型，因此 ID 不是特定于一种类型，而是一种多对多的关系.我想要一个查询来查找恰好有 4 种类型的电影总数.我当前的查询是

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 

  SELECT COUNT(*) 
    FROM Movies 
GROUP BY ID 
  HAVING COUNT(Genre) = 4

然而，这会返回一个 4 的列表而不是总和.如何获得总和而不是 count(*) 的列表?

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(*)?

推荐答案

一种方法是使用嵌套查询:

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

内部查询获取恰好有 4 种类型的所有电影，然后外部查询计算内部查询返回的行数.

The inner query gets all the movies that have exactly 4 genres, then outer query counts how many rows the inner query returned.

相关文章