SQL n对n匹配多个值

Question

我有一个与标签相匹配的网络应用程序，需要创建一种动态细化标签搜索结果的方法。然而，我找不到一个干净的方式来进行SQL查询，这就是我需要你的帮助的地方。

我的想法是，如果我搜索标签“clean”和“dog”，我将得到同时具有“clean”和“dog”标签的图像结果。如果我还包括标签“little”，我的结果必须缩小到具有这三个标签关联的图像。

因此，由于存在N对N的关系，什么是正确的处理方式呢？

我的自然方法是生成类似于这样的代码，但我肯定不喜欢它的发展方向:

SELECT images.*
FROM images
INNER JOIN image_tags ON ...
INNER JOIN tags ON ...
WHERE tags.tag = @tag1
AND EXISTS
(
  SELECT 1
  FROM images 
  INNER JOIN image_tags ON ...
  INNER JOIN tags ON ...
  WHERE tag = @tag2
  AND EXISTS
  (
    SELECT 1
    FROM images 
    INNER JOIN image_tags ON ...
    INNER JOIN tags ON ...
    WHERE tag = @tag3
    AND EXISTS (...)
    ...
  )
)

当然，这并不是很好。有什么想法吗？

谢谢！

- Alpha

Answer 1

这个方法可能是可行的（我使用id进行SELECT和GROUP BY，使用你需要的列）。

SELECT images.id
FROM images
INNER JOIN image_tags ON ...
INNER JOIN tags ON ...
WHERE tags.tag IN ( @tag1, @tag2, @tag3 )
GROUP BY images.id
HAVING COUNT(*) = @number_of_tags

如果您的示例中有3个标签，那么number_of_tags必须是3，并且连接将导致每个匹配的id有3行结果。

您可以动态创建该查询，也可以定义它，例如使用10个标签并将其初始化为不会出现在标签中的值。

- Peter Lang

---

这在允许/要求的标签数量方面非常严格，而且会为每个指定的标签返回一行，而不是为每个图像返回一行。 - Nathan Wheeler

GROUP BY 应该避免为每个标签返回一行。我编辑了问题以展示它如何处理动态数量的标签。 - Peter Lang

非常感谢！我没有想到要使用HAVING COUNT()重新检查结果。 - Alpha

Answer 2

我不会使用N-N关系，而是使用文本字段来存储标签。

这可能听起来有些不规范，但标签通常只用于文本搜索，而且磁盘空间很便宜。

然后你就可以运行

SELECT * FROM images WHERE tags LIKE '%clean%' AND tags LIKE '%dog%'...

- Benoît Vidis

---

@Peter - 字母排序做得不错... d在c之后... ;-) - Nathan Wheeler

注意：您的解决方案将使计算每个标签的图像数量以及重命名或删除标签变得更加复杂。 - Peter Lang

@md5sum：哎呀！抱歉，我不得不删除那篇帖子 ;-） - Peter Lang

在插入标签时，您可以按字母顺序排序。这将允许使用一个LIKE语句进行SELECT查询：SELECT * FROM images WHERE tags LIKE '%clean%dog%'。 - Peter Lang

@md5sum：非常感谢，当我读到你的帖子时也让我笑了;-) - Peter Lang

Answer 3

使用“intersect”函数，你可以这样做：

SELECT images.* 
FROM images 
WHERE image_id IN 
  (
    SELECT image_id FROM image_tags WHERE tag_id =
      (SELECT tag_id FROM tags WHERE tag = @tag1)
    INTERSECT
    SELECT image_id FROM image_tags WHERE tag_id =
      (SELECT tag_id FROM tags WHERE tag = @tag2)
    INTERSECT
      ....
   )

这将根据image_tags中的交集（匹配所有）标签选择所有图像。

- rosscj2533