需要有关 sql 查询的帮助以查找带有所有指定标签的内容

See also: Need help with sql query to find things with most specified tags (note: similar, but not a duplicate!)

推荐答案

使用 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)

使用连接

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','Wine','Paris','Frace','City','Scenic','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','Wine','Paris','Frace','City','Scenic','Art')
                  AND tg.post_id = p.id
             GROUP BY tg.post_id
               HAVING COUNT(DISTINCT t.name) = 7)

说明

关键是 COUNT(DISTINCT t.name) 需要匹配标签名称的数量，以确保所有这些标签都与帖子相关.如果没有 DISTINCT，其中一个名称的重复可能会返回 7 的计数，因此您会误报.