mysql - SQL-配对电影体裁

我有一张把电影类型映射到电影的表。

GENRE       | MOVIE
--------------------------
Drama       | Boring Movie
Action      | Boring Movie
Comedy      | Boring Movie
Documentary | Epic Movie
Romantic    | Epic Movie
Action      | Epic Movie
Drama       | Epic Movie
Action      | Lost
Drama       | Lost
---------------------------

我想写一个MySQL语句，告诉我哪两种类型最常链接到同一部电影这里的答案是动作/戏剧，因为它们在所有3部电影中都是成对的。

最佳答案

您需要执行自连接来处理“链接到同一个电影”的要求，但不能连接到同一个确切的记录。

SELECT movie_genre.genre, peer.genre as peer_genre, COUNT(*)
FROM movie_genre
JOIN movie_genre AS peer
  ON movie_genre.MOVIE = peer.MOVIE
  AND movie_genre.GENRE < peer.GENRE
GROUP BY 1,2
ORDER BY 3 DESC ,1
LIMIT 1

更具可读性的解决方案

SELECT movie_genre.genre AS Genre1, peer.genre as Genre2, COUNT(*) As Relations
FROM movie_genre
JOIN movie_genre AS peer
  ON movie_genre.MOVIE = peer.MOVIE
    AND movie_genre.GENRE < peer.GENRE
GROUP BY Genre1, Genre2
ORDER BY Relations desc ,Genre1
LIMIT 1