sql - Postgres - 具有 WHERE AND 条件的单列上的多个条件？

Question

所以我有一个查询如下:

SELECT AVG(ratings.rating)
FROM ratings
INNER JOIN movies 
ON movies.movieid = ratings.movieid
INNER JOIN hasagenre
ON hasagenre.movieid = movies.movieid
INNER JOIN genres
ON hasagenre.genreid = genres.genreid
WHERE genres.name = 'Comedy'

最后一行对此最重要，它获取所有COMEDY 电影的平均评分。

但总的来说，我有几个与电影数据库相关的表，例如 movies(title,id)、genres(name,id)、ratings (movie,rating),hasagenre(movie,genre).....例如:

Movie     | Genre
------------------
Get Smart | Comedy 
Get Smart | Romance 
Scream    | Horror 

然后我想编写一个查询，以获取同时是COMEDY 和ROMANCE 的电影的平均评分，例如Get Smart。因此，我简单地更新了查询的最后一行如下:

SELECT AVG(ratings.rating)
FROM ratings
INNER JOIN movies 
ON movies.movieid = ratings.movieid
INNER JOIN hasagenre
ON hasagenre.movieid = movies.movieid
INNER JOIN genres
ON hasagenre.genreid = genres.genreid
**WHERE (genres.name = 'Comedy' AND genres.name = 'Romance')**

但是，这不起作用并返回 0 行，即使我确实知道数据库中有满足这两种类型的电影。这里出了什么问题？

同一列上的多条件 AND 子句的正确语法是什么？

Answer 1

如果您连接所有表格以形成平均值，由于涉及多种类型，您将乘以行数。而是首先找出哪些电影满足 2 类型条件，然后对这些电影的收视率进行平均。

SELECT
    AVG(ratings.rating)
FROM ratings
INNER JOIN (
    SELECT
        hasagenre.movieid
    FROM hasagenre
    INNER JOIN genres ON hasagenre.genreid = genres.genreid
    WHERE genres.Name IN ('Comedy', 'Romance')
    GROUP BY
        hasagenre.movieid
    HAVING COUNT(DISTINCT genres.Name) = 2
) m ON ratings.movieid = m.movieid