需要有关SQL查询的帮助，以查找带有所有指定标签的事物 [英] Need help with sql query to find things tagged with all specified tags

问题描述

假设我有以下表格:

id:整数
名称:字符串

id: integer
name: string

id:整数
正文:文本

id: integer
body: text

id:整数
tag_id:整数
post_id:整数

id: integer
tag_id: integer
post_id: integer

我将如何编写一个查询，选择所有带有以下所有标记(标记表的名称属性)标记的帖子:奶酪"，葡萄酒"，巴黎"，宽限期"，城市" "，风景"，艺术"

How would I go about writing a query that select all posts that are tagged with ALL of the following tags (name attribute of tags table): "Cheese", "Wine", "Paris", "Frace", "City", "Scenic", "Art"

另请参阅:需要有关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)

使用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','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的计数，因此您可能会有误报.

Explanation

The crux of things is that the COUNT(DISTINCT t.name) needs to match the number of tag names to ensure that all those tags are related to the post. Without the DISTINCT, there's a risk that duplicates of one of the names could return a count of 7--so you'd have a false positive.

大多数人会告诉您JOIN是最佳的，但JOIN还会冒着重复结果集中的行的风险. EXISTS是我的下一个选择-不会有重复的风险，并且执行速度通常会更快，但是检查说明计划最终将根据您的设置和数据告诉您哪种方法最好.

Most will tell you the JOIN is optimal, but JOINs also risk duplicating rows in the resultset. EXISTS would be my next choice--no duplicate risk, and generally faster execution but checking the explain plan will ultimately tell you what's best based on your setup and data.

这篇关于需要有关SQL查询的帮助，以查找带有所有指定标签的事物的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！