MySQL - 一个表，多行具有重复的id

Question

所以我有一个“流派”表，其中包含重复的流派和一个 ID，我想制定一个查询，以便返回具有特定 ID 的两种流派的所有行。

例如，

+---------+-------+
| genre   | movie |
+---------+-------+
| Musical |   558 |
| Musical |   562 |
| Musical |   597 |
| Musical |   651 |
| Musical |   656 |
| Musical |   791 |
| Musical |   810 |
| Musical |   845 |
| Musical |   859 |
| Musical |   919 |
| Musical |   949 |
| Musical |   971 |
+---------+-------+
12 rows in set (0.00 sec)

和

+--------+-------+
| genre  | movie |
+--------+-------+
| Comedy |   642 |
| Comedy |   643 |
| Comedy |   644 |
| Comedy |   651 |
| Comedy |   654 |
| Comedy |   658 |
+--------+-------+
6 rows in set (0.00 sec)

我希望它返回“电影 651”，因为它既有喜剧又有音乐(假设同时查询“喜剧”和“音乐”)。

Answer 1

使用

SELECT DISTINCT A.MOVIE FROM GENRES A, GENRES B WHERE A.MOVIE = B.MOVIE AND A.GENRE = 'Comedy' AND B.GENRE = 'Musical';

编辑 - 根据评论:

SELECT DISTINCT A.MOVIE 
FROM
    GENRES A 
  INNER JOIN
    GENRES B
        ON A.MOVIE = B.MOVIE
WHERE A.GENRE = 'Comedy'
  AND B.GENRE = 'Musical';