查询以搜索包含所有请求的标记的歌曲。

Question

我正在使用标签制作歌曲搜索引擎，我在构建SQL查询时遇到了问题，该查询将列出所有与标记匹配的歌曲。

数据库如下所示：http://i.imgur.com/5zmfAz8.png

歌曲通过中间表(SongTags)有许多标记。

让我们以人口为例：

标签：电子，器乐，精力充沛，忧郁，声乐，摇滚

：SongA (Electro，忧郁，声乐) SongB (器乐，忧郁，摇滚) SongC (精力充沛，声乐)

搜索应该返回包含所请求的所有标记的歌曲。

Search1:

“声乐”返回: SongA，SongB

Search2:

“声乐”、“精力充沛”的回报: SongC

Search3:

“声乐”、“精力充沛”、“电子”回归:什么都没有

我知道如何对1标签进行搜索，而不是在多个标签上进行搜索。以Search1为例，我知道这是可行的：

SELECT * FROM "songs"
INNER JOIN "song_tags" ON "song_tags"."song_id" = "songs"."id"
INNER JOIN "tags" ON "tags"."id" = "song_tags"."tag_id"
WHERE "tags"."name" = 'Vocal'

但是我不知道如何执行Search2，因为我需要这首歌同时包含“声乐”和“精力充沛”。

编辑：

我正在使用PostgreSQL

Answer 1

SELECT songs.*, COUNT(songs.id) AS total FROM "songs"
INNER JOIN "song_tags" ON "song_tags"."song_id" = "songs"."id"
INNER JOIN "tags" ON "tags"."id" = "song_tags"."tag_id"
WHERE "tags"."name" IN ('Vocal', 'Energetic')
GROUP BY songs.id
HAVING total = 2

Answer 2

如果你想得到与你所提供的所有标签相匹配的歌曲，我会使用子选择。它们会有点慢，但它会给你一个完全匹配的解决方案：

SELECT songs.* FROM songs
  WHERE songs.song_id IN
    (SELECT song_tags.song_id FROM song_tags st JOIN tags t ON st.tag_id = t.id AND t.name = 'VOCAL')
   AND songs.song_id IN (SELECT song_tags.song_id FROM song_tags st JOIN tags t ON st.tag_id = t.id AND t.name = 'ENERGETIC')

如果您的SQL服务器支持INTERSECT命令，则可以这样做：

SELECT songs.* FROM songs
  WHERE songs.song_id IN
    (SELECT song_tags.song_id FROM song_tags st JOIN tags t ON st.tag_id = t.id AND t.name = 'VOCAL' 
    INTERSECT
   SELECT song_tags.song_id FROM song_tags st JOIN tags t ON st.tag_id = t.id AND t.name = 'ENERGETIC')

我觉得这是个更酷的方法。

希望这能有所帮助。:)