sql - 选择在 SQL 中具有精确指定关联的条目

Question

例如，如果某个实体可以有多个标签，并且使用entity_tag包含entity_id和的附加表来描述关联tag_id，我如何选择所有具有某些tag_ids 集的实体？

基本上是这样的：

select entity_id 
from entity_tag et 
where (select tag_id 
       from entity_tag
       where entity_id = et.entity_id) = (1, 2, 3)

标签： sql

Answer 1

查找 entity_tag 包含实体的所有 3 个标签的实体，甚至更多。

select entity_id 
from entity_tag 
where tag_id in (1,2,3)
group by entity_id 
having count(distinct tag_id)=3

3个标签

select entity_id
from entity_tag 
group by entity_id 
having count(distinct case when tag_id in (1,2,3) then tag_id else 0 end) = 3
and min(case when tag_id in (1,2,3) then tag_id else 0 end) > 0

小提琴