sqlite - Sqlite：查找具有共同值的记录对

Question

我有两个看起来像这样的表：

movie_cast
movie_id | cast_id | cast_name
10         2         Mark Hamill
10         3         Harrison Ford
10         4         Carrie Fisher
12         10        James Earl Jones
12         2         Mark Hamill
12         3         Harrison Ford
12         4         Carrie Fisher

movies
id   |   title
10       Star Wars IV
12       Star Wars V

我需要创建一个查询，返回至少一起出演过 2 部电影的演员对。

所以它应该看起来像这样：

cast_member_id1 | cast_member_id2
2                 3
3                 4

我通常会输入自己的查询，但我不知道从哪里开始在 SQLite 中获取对。

标签： sqlite

Answer 1

您可以生成所有具有自联接和一些分组的唯一参与者对，使用min()和max()获取规范顺序。然后使用带有另一个自连接的子查询来获取两者共享电影的次数：

WITH cast_pairs AS
  (SELECT min(a.cast_id,b.cast_id) AS cast_member_id1,
          max(a.cast_id,b.cast_id) AS cast_member_id2
   FROM movie_cast AS a
   JOIN movie_cast AS b ON a.cast_id <> b.cast_id
   GROUP by 1,2)
SELECT *
FROM cast_pairs
WHERE (SELECT count(*)
       FROM movie_cast AS m1
       JOIN movie_cast AS m2 ON m1.movie_id = m2.movie_id
       WHERE m1.cast_id = cast_member_id1 AND m2.cast_id = cast_member_id2) >= 2;

这使

cast_member_id1  cast_member_id2
---------------  ---------------
2                3              
2                4              
3                4