mysql 标签查询_需要sql查询帮助来查找标记有所有指定标签的内容

使用IN：

SELECT p.*

FROM POSTS p

WHERE p.id IN (SELECT tg.post_id

FROM TAGGINGS tg

JOIN TAGS t ON t.id = tg.tag_id

WHERE t.name IN ('Cheese','Wine','Paris','Frace','City','Scenic','Art')

GROUP BY tg.post_id

HAVING COUNT(DISTINCT t.name) = 7)

使用JOIN

SELECT p.*

FROM POSTS p

JOIN (SELECT tg.post_id

FROM TAGGINGS tg

JOIN TAGS t ON t.id = tg.tag_id

WHERE t.name IN ('Cheese','Art')

GROUP BY tg.post_id

HAVING COUNT(DISTINCT t.name) = 7) x ON x.post_id = p.id

使用EXISTS

SELECT p.*

FROM POSTS p

WHERE EXISTS (SELECT NULL

FROM TAGGINGS tg

JOIN TAGS t ON t.id = tg.tag_id

WHERE t.name IN ('Cheese','Art')

AND tg.post_id = p.id

GROUP BY tg.post_id

HAVING COUNT(DISTINCT t.name) = 7)

说明

问题的关键是COUNT(DISTINCT t.name)需要匹配标记名称的数量,以确保所有这些标记与帖子相关.如果没有DISTINCT,那么其中一个名称的重复可能会返回7的风险 – 所以你会有误报.

大多数人会告诉你JOIN是最优的,但是JOIN还冒着重复结果集中的行的风险. EXISTS将是我的下一个选择 – 没有重复的风险,通常执行速度更快,但检查解释计划最终将根据您的设置和数据告诉您什么是最好的.