查询以查找带有一组精确标签的帖子(多对多关系) [英] Query to find posts with an exact set of tags (many-to-many relationship)
问题描述
假设我有以下三个表来表达一种关系,其中帖子被赋予标签(多对多关系):
Suppose I have the following three tables expressing a relationship where posts are given tags (a many-to-many relationship):
create table posts (id integer, content text, primary key (id));
create table tags (tag varchar(30), description text, primary key (tag));
create table post_tags (post_id integer, tag varchar(10),
primary key (post_id, tag),
foreign key (post_id) references posts (id),
foreign key (tag) references tags (tag));
现在假设我想查找所有带有标签{clever,interesting},而没有其他标签的帖子.
Now suppose I want to find all posts with exactly the tags {clever,interesting}, and no others.
这是我失败的尝试.它会找到带有标签{clever,interesting}的帖子,但还会找到带有标签{clever,interesting,annoying}或{clever,interesting,unthical}的帖子.
Here is my failed attempt. It finds posts which have the tags {clever,interesting}, but it also finds posts with the tags {clever,interesting,annoying} or {clever,interesting,unethical}.
select t1.post_id from post_tags as t1
inner join post_tags as t2 on t2.post_id=t1.post_id
where t1.tag='clever' and t2.tag='interesting';
我乐于接受任何有关更改结构以简化此操作的建议.但是,我想避免在posts
表中添加逗号分隔的标签列表之类的事情.
I'm open to any suggestions for changing the structure to make this operation easier. However, I want to avoid things like adding a comma-separated list of tags in the posts
table.
推荐答案
这是一个精确的关系划分问题.
This is an exact relational division problem.
在SQL Server中,运行良好方法(假设对post_id,tag
的唯一约束)为
In SQL Server a well performing method (assuming unique constraint on post_id,tag
) is
SELECT post_id
FROM post_tags
GROUP BY post_id
HAVING MIN(CASE
WHEN Keyword IN ( 'clever', 'interesting' ) THEN 1
ELSE 0
END) = 1
AND SUM(CASE
WHEN Keyword IN ( 'clever', 'interesting' ) THEN 1
ELSE 0
END) = 2
所以我不排除在HAVING
中使用GROUP_CONCAT
的想法.
So I wouldn't rule out the idea of using GROUP_CONCAT
in the HAVING
instead.
HAVING GROUP_CONCAT(DISTINCT Keyword ORDER BY Keyword) = 'clever,interesting'
这篇关于查询以查找带有一组精确标签的帖子(多对多关系)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!