需要有关 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)

使用连接

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屋！