SQL MySQL：如何在WHERE IN中判断类似于OR的AND逻辑

发布于08月26日

我有两张桌子，名字分别是item和tag.它们具有多对多关系，因此它们的连接表是item_tag，如下所示.

-- item table
id  name
1   Item 1
2   Item 2
3   Item 3

-- tag table
id  name
1   Tag 1
2   Tag 2
3   Tag 3

-- item_tag table
item_id tag_id
1       1
2       1
2       2
3       1
3       3

我需要一个SQL查询来获取具有both、Tag 1和Tag 2(给定标签ID的AND操作=(1，2))的项目.

也就是说，

-- Expected output
id  name
2   Item 2

只有Item 2有both、Tag 1和Tag 2 因此，标签的逻辑应该是AND.

[WHERE IN为此场景给出了类似的OR逻辑，因此不能使用它]

有没有人能帮我写这个查询？

谢谢!

推荐答案

获取同时具有标记1和标记2的项目

SELECT *
FROM item
WHERE EXISTS ( SELECT NULL
               FROM item_tag 
               WHERE item.id = item_tag.item_id
                 AND tag_id = 1 )
  AND EXISTS ( SELECT NULL
               FROM item_tag 
               WHERE item.id = item_tag.item_id
                 AND tag_id = 2 )

当我有更多的标记ID时，需要通过 for each 标记ID添加子查询来修改此查询.

不需要修改的查询:

SELECT item.id, item.name
FROM item
JOIN item_tag ON item.id = item_tag.item_id
WHERE item_tag.tag_id IN ( {tags list} )
GROUP BY 1,2
HAVING COUNT( DISTINCT item_tag.tag_id ) = {tags list length}

如果item_tag表 struct 中存在按(item_id, tag_id)的唯一索引，则可以删除DISTINCT.

如果您有标记名称列表，而不是ID列表，则:

SELECT item.id, item.name
FROM item
JOIN item_tag ON item.id = item_tag.item_id
JOIN tag ON item_tag.tag_id = tag.id
WHERE tag,name IN ( {tag names list} )
GROUP BY 1,2
HAVING COUNT( DISTINCT item_tag.tag_id ) = {tags list length}