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

问题描述

假设我有一个包含两列的标签"表:tagid 和 contentid.每行代表一个分配给一段内容的标签.我想要一个查询，该查询将为我提供标记为 tagids 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.

执行此操作的简单"方法是(伪代码):

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?

编辑:事实证明这也包含在 这篇优秀的博文.

EDIT: Turns out that this is also covered in this excellent blog post.

推荐答案

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