尝试根据某些 ID 过滤从 PostgreSQL 返回的结果时遇到一些问题

Question

由于无聊，我决定今天为我的电影设置一个简单的标签系统。我基本上有一个电影标题列表和每部电影的 X 个标签。数据库看起来像这样：

mydb=# select id, title from movies;

 id |                  title                   
----+----------------------------------------
  1 | first_movie
  2 | second_movie

mydb=# select id, movie_id, tag_id from movie_tags;

 id | movie_id | tag_id 
----+----------+--------
  1 |        1 |    716
  2 |        1 |    787
  3 |        1 |    322
  4 |        2 |    716
  5 |        2 |    787
  6 |        2 |    323

mydb=# SELECT l.id, l.title, t.tag_id FROM movies l, movie_tags t WHERE t.movie_id = l.id AND t.tag_id IN(716, 787, 323);

 id |    title     | tag_id 
----+--------------+--------
  2 | second_movie |    787
  2 | second_movie |    716
  2 | second_movie |    323
  1 | first_movie  |    716
  1 | first_movie  |    787

这是第三个查询给我带来了问题。首先，它显示重复的行。每个 tag_id 一个新行。我不想要这个。我希望它为每部电影显示与指定标签匹配的一行。

这引出了第二个问题。如您所见，first_movie 未使用 tag_id=323 标记。然而，它仍然出现在结果中。

如何不显示重复行并将结果过滤到与所有给定标签匹配的电影？

我计划为此构建一个简单的 UI，因此我希望有一个很好的“动态”查询，我可以将 tag_ids 列表放入其中。

Answer 1

此查询为您提供搜索到所有 3 个标签的所有电影。

  SELECT l.id, l.title
    FROM movies l, movie_tags t
   WHERE t.movie_id = l.id
     AND t.tag_id IN (716, 787, 323)
GROUP BY l.id, l.title
  HAVING COUNT(*) = 3

正如您所看到的，标签列已被删除，因为包含一个任意标签是没有意义的。

Answer 2

SELECT l.id, l.title
FROM movies l
WHERE l.id IN (SELECT t.movie_id 
               FROM movie_tags t 
               WHERE t.tag_id IN (716, 787, 323));