选择与多个标记匹配的记录

Question

我在带有两个表的SQLite上实现了一个标准标记系统。

表annotation:

CREATE TABLE IF NOT EXISTS annotation (
id INTEGER PRIMARY KEY,
comment TEXT
)

表label:

CREATE TABLE IF NOT EXISTS label (
id INTEGER PRIMARY KEY,
annot_id INTEGER NOT NULL REFERENCES annotation(id),
tag TEXT NOT NULL
)

我可以很容易地找到与标签"tag1"或"tag2"匹配的注释：

SELECT * FROM annotation
JOIN label ON label.annot_id = annotation.id
WHERE label.tag IN ('tag1', 'tag2') GROUP BY annotation.id

1. 但是如何选择与标记"tag1"AND匹配的注释"tag2">
2. 如何选择与标记"tag1"匹配的注释和"tag2"而不是"tag3">

我应该使用INTERSECT吗？它是有效的还是有更好的方式来表达这些？

Answer 1

对于问题1和问题2，我肯定会选择INTERSECT。经过多年的SQL经验，我发现在直接解决您想做的事情的情况下，最好使用平台提供的任何东西。

唯一的例外是，如果你有充分的理由不这样做。在这种情况下，intersect和except不是ansi标准，所以只要你使用它们，你就会一直使用sqlite。

如果你想老派，只使用直接的SQL，可以使用子查询，一个用于标记A，一个用作标记B，还有一个用于标签C；为空"；condition是一个常见的执行排除的成语。下面是一个sqlite示例：

create table annotation (id integer, comment varchar);
create table label (id integer, annot_id integer, tag varchar);
insert into annotation values (1,'annot 1'),(2,'annot 2');
insert into label values (1,1,'tag1'),(2,1,'tag2'),(3,1,'tag2');
insert into label values (1,2,'tag1'),(2,2,'tag2'),(3,2,'tag3');
select distinct x.id,x.comment from annotation x
join label a on a.annot_id=x.id and a.tag='tag1'
join label b on b.annot_id=x.id and b.tag='tag2'
left join label c on c.annot_id=x.id and c.tag='tag3'
where 
c.id is null;

这样设置使得注释1和2都具有标签1和标签2，但标签2具有标签3，因此应排除输出仅为注释1:

id	comment
1	注释1