我正在为网站实施标记系统。每个对象有多个标签，每个标签有多个对象。这是通过维护一个表来实现的，该表每条记录具有两个值，一个用于对象ID和标签的ID。

我正在寻找编写查询以查找与给定标记集匹配的对象。假设我有以下数据([对象]-> [标签] *格式)

apple -> fruit red food
banana -> fruit yellow food
cheese -> yellow food
firetruck -> vehicle red

如果要匹配(红色)，我应该拿苹果和救火车。如果我想搭配(水果，食物)，我应该搭配(苹果，香蕉)。

我该如何编写SQL查询来做我想做的事情？

@杰里米·鲁滕(Jeremy Ruten)，

感谢回答。所使用的表示法是用于提供一些示例数据的，我的数据库确实具有一个表，该表具有1个对象ID和每个记录1个标签。

其次，我的问题是我需要获取与所有标签匹配的所有对象。将OR替换为AND，如下所示：

SELECT object WHERE tag = 'fruit' AND tag = 'food';

运行时不产生任何结果。

解决方案

回答

我建议使表的每条记录具有1个标签，如下所示：

apple -> fruit
 apple -> red
 apple -> food
 banana -> fruit
 banana -> yellow
 banana -> food

那你就可以

SELECT object WHERE tag = 'fruit' OR tag = 'food';

如果我们确实想按照自己的方式进行操作，则可以这样做：

SELECT object WHERE tag LIKE 'red' OR tag LIKE '% red' OR tag LIKE 'red %' OR tag LIKE '% red %';

回答

@Kyle：查询应该更像是：

SELECT object WHERE tag IN ('fruit', 'food');

查询正在寻找标签既是水果又是食物的行，由于该字段只能具有一个值，而不能同时具有两个值，因此无法看到行。

回答

鉴于：

• 对象表(主键ID)
• objecttags表(外键objectId，tagid)
• 标签表(主键ID)

SELECT distinct o.*
  from object o join objecttags ot on o.Id = ot.objectid
                join tags t on ot.tagid = t.id
 where t.Name = 'fruit' or t.name = 'food';

这似乎是向后的，因为我们想要，但问题是2个标记不在同一行，因此an和不会产生任何结果，因为1行不能既是水果又是食物。
此查询通常会产生重复项，因为每个标签每个对象将获得1行。

如果我们确实希望做一个并且在这种情况下，则需要在查询中使用"分组依据"，并且需要"计数= <ors数>"。

SELECT distinct o.name, count(*) as count
    from object o join objecttags ot on o.Id = ot.objectid
                  join tags t on ot.tagid = t.id
   where t.Name = 'fruit' or t.name = 'food'
group by o.name
  having count = 2;

回答

哦，天哪，我可能误解了原始评论。

在SQL中执行此操作的最简单方法是拥有三个表：

1) Tags ( tag_id, name )
2) Objects (whatever that is)
3) Object_Tag( tag_id, object_id )

然后，我们几乎可以快速，轻松，有效地对数据提出任何想要的问题(前提是我们正确地建立了索引)。如果我们想花哨的话，也可以使用多字标签(我想这是一种优雅的方法，而一种不太优雅的方法)。

我假设这就是我们所拥有的，因此下面的此SQL将起作用：

字面方式：

SELECT obj 
      FROM object
     WHERE EXISTS( SELECT * 
                     FROM tags 
                    WHERE tag = 'fruit' 
                      AND oid = object_id ) 
       AND EXISTS( SELECT * 
                     FROM tags 
                    WHERE tag = 'Apple'
                      AND oid = object_id )

我们还可以通过其他方式进行操作，例如：

SELECT oid
  FROM tags
 WHERE tag = 'Apple'
INTERSECT
SELECT oid
  FROM tags
 WHERE tag = 'Fruit'

回答

结合Steve M.的建议和Jeremy's，我们将获得一份单一的记录，其中包含我们要寻找的内容：

select object
from tblTags
where tag = @firstMatch
and (
       @secondMatch is null 
       or 
       (object in (select object from tblTags where tag = @secondMatch)
     )

现在，这种方法无法很好地扩展，但是可以满足需求。我认为有一个更好的方法可以执行此操作，因此我们可以轻松拥有N个匹配项，而不会对代码造成很大影响，但目前它使我不知所措。

回答

我建议使用以下架构。

Objects: objectID, objectName
Tags: tagID, tagName
ObjectTag: objectID,tagID

用以下查询。

select distinct
    objectName
from
    ObjectTab ot
    join object o
        on o.objectID = ot.objectID
    join tabs t
        on t.tagID = ot.tagID
where
    tagName in ('red','fruit')