假设我有一个带有两列的"标签"表：tagid和contentid。每行代表分配给一条内容的标签。我想要一个查询，该查询将为我提供标记为334、338和342的每段内容的contentid。

做到这一点的"简单"方法是(伪代码)：

select contentid from tags where tagid = 334 and contentid in (
    select contentid from tags where tagid = 338 and contentid in (
        select contentid from tags where tagid = 342
    )
)

但是，我的直觉告诉我，有一种更好，更快，更可扩展的方法来执行此操作。例如，如果我需要找到12个标签的交集怎么办？这可能很快变得可怕。有任何想法吗？

编辑：事实证明，这本出色的博客文章中也对此进行了介绍。

解决方案

我能想到的唯一替代方法是：

select a.contentid from tags a
inner join tags b on a.contentid = b.contentid and b.tagid=334
inner join tags c on a.contentid = c.contentid and c.tagid=342
where a.tagid=338

SELECT contentID
FROM tags
WHERE tagID in (334, 338, 342)
GROUP BY contentID
HAVING COUNT(DISTINCT tagID) = 3

--In general
SELECT contentID
FROM tags
WHERE tagID in (...) --taglist
GROUP BY contentID
HAVING COUNT(DISTINCT tagID) = ... --tagcount

什么类型的SQL？ MS SQL Server，Oracle，MySQL？

在SQL Server中，这不等于：

select contentid from tags where tagid IN (334,338,342)

我不知道这是否更好，但可能更易于维护

select contentid from tags where tagid = 334
intersect
select contentid from tags where tagid = 338
intersect
select contentid from tags where tagid = 342

我们必须动态地构建它，这不会像原始解决方案那样糟糕。

在一个非常大的对象和标签数据库中，这个解决方案的工作速度比对我而言快得多。这是一个三标签路口的示例。它只是在对象标签表(objtags)上链接许多联接以指示同一对象，并在WHERE子句中规定了标签ID：

SELECT w0.objid

FROM       objtags t0
INNER JOIN objtags t1 ON t1.objid=t0.objid
INNER JOIN objtags t2 ON t2.objid=t1.objid

WHERE t0.tagid=512
  AND t1.tagid=256
  AND t2.tagid=128

我不知道为什么运行速度更快。它的灵感来自MusicBrainz服务器中的搜索代码。在Postgres中执行此操作，通常比" HAVING COUNT(...)"解决方案的速度提高约8-10倍。