如何在SQL中实现布尔标签搜索?

Given a table of items, a table of tags and a join table between them, what is a good and efficient way to implement queries of the form:

给定一个项目表、一个标记表和它们之间的一个连接表，如何有效地实现表单查询:

p1 AND p2 AND ... AND pn AND NOT n1 AND NOT n2 ... AND NOT nk

p1 p2和…而pn，不是n1而不是n2…而非朝鲜

I am using SQL. So to find all items that match all tags p1...pn and none of n1...nk?

我使用SQL。为了找到所有匹配p1标签的项…pn和n1, nk?

Is there a good "standard" solution for this?

有好的“标准”解决方案吗?

5 个解决方案

#1

Difficult to say without knowing your schema, but something like this would work:

如果不知道你的模式，很难说，但是这样的方法是可行的:

select article_id from articles
inner join tag t1 on t1.article_id=articles.article_id and t1.tag='included_tag'
inner join tag t2 on t2.article_id=articles.article_id and t2.tag='another_included_tag'
left outer join tag t3 on t3.article_id=articles.article_id and t3.tag='dont_include_tag'
left outer join tag t4 on t4.article_id=articles.article_id and t4.tag='also_dont_include_tag'
where t3.tag_id is null and t4.tag_id is null

inner join to tags that are to be included, and do an anti-join (outer join + where a required column is null) to tags that are not to be included

要包含的标记的内部连接，并对不包含的标记执行反连接(外部连接+，其中所需列为空)

#2

I think this is what you're looking for:

我想这就是你要找的:

SELECT * FROM TABLE_NAME WHERE COLUMN1 IN ('value1','value2','value3') AND COLUMN1 NOT IN ('value4','value5','value6')

If not, let me know. I may have misunderstood your question.

如果没有，请告诉我。我可能误解了你的问题。

#3

It depends on how you're storing tags in the database, but you probably want the IN operator:

这取决于您如何在数据库中存储标记，但您可能需要in操作符:

SELECT tag FROM myTags WHERE tag IN ('p1','p2',...)
SELECT tag FROM myTags WHERE tag NOT IN ('p1','p2',...)

#4

SELECT DISTINCT itemID FROM ItemsTags it, Tags t 
WHERE it.tagID = t.ID AND t.tag IN ('p1','p2','p3') AND t.tag NOT IN ('p4','p5','p6')

#5

SELECT i.title
  FROM items i
 WHERE EXISTS(SELECT * FROM join_table j JOIN tags t ON t.id = j.tag_id WHERE j.item_id = i.id AND t.name = 'tag1')
   AND NOT EXISTS(SELECT * FROM join_table j JOIN tags t ON t.id = j.tag_id WHERE j.item_id = i.id AND t.name = 'tag2')

SQL server does a good job about this construct, but Oracle might need some hinting to get it right (at least it did 5 years ago).

SQL server在这个构造上做得很好，但是Oracle可能需要一些提示才能使其正确(至少5年前是这样)。

#1