SQL查询:模拟“与"多行而不是子查询 [英] SQL query: Simulating an "AND" over several rows instead of sub-querying

问题描述

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

Suppose I have a "tags" table with two columns: tagid and contentid. Each row represents a tag assigned to a piece of content. I want a query that will give me the contentid of every piece of content which is tagged with tagids 334, 338, and 342.

做到这一点的简便"方法是( pseudocode ):

The "easy" way to do this would be (pseudocode):

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个标签的交集怎么办?这可能很快变得可怕.有什么想法吗?

However, my gut tells me that there's a better, faster, more extensible way to do this. For example, what if I needed to find the intersection of 12 tags? This could quickly get horrendous. Any ideas?

编辑:事实证明，推荐答案

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查询:模拟“与"多行而不是子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！