Mysql多对多关系查询。如何获取过滤后帖子的所有标签？

Question

我已经在 Stackoverflow 中解决了很多关于这个问题的问题，但我认为这是不同的。

我想做的是让用户能够按标签过滤帖子，这样用户就只会看到要过滤的标签。换句话说，如果用户选择标签“tag1”，它会显示带有该标签的帖子，还会显示其他发布分享的标签，但会隐藏过滤后没有可见帖子的标签。

我有表 posts、posts_tags 和 tags。 posts_tags 有 post_id 和 tag_id。我已经设法让 post_ids 可用于特定的标签集:

SELECT pt.post_id
FROM posts_tags pt
    INNER JOIN tags t
        ON pt.tag_id = t.id
WHERE t.name IN ('tag1', 'tag2', 'tag3')
GROUP BY pt.post_id
HAVING COUNT(DISTINCT t.id) = 3;

假设此查询给出 post_ids 1、2、3:

post 1 has tag1, tag2, tag3 and tag4
post 2 has tag1, tag2, tag3 and tag5
post 3 has tag1, tag2, tag3 and tag6

现在我的问题是如何扩展查询以仅返回 tag4、tag5 和 tag6 给用户，因为这些标签仍然可用于进一步过滤帖子。如何实现？

注意性能也不错。我有 130000 个帖子，6500 个标签，桥接表有 240000 行。

编辑:使用场景:

1. 用户使用自动完成功能搜索标签并选择多个标签。
2. 用户根据提交的标签检索帖子。

3. 用户搜索更多标签，此时:

   我不想给出完整的列表，只给出那些

   一个。那还没有被选中。

   在第 2 步检索到的帖子中使用。

---

编辑:基于 Mosty Mostacho 的答案的最终查询:

SELECT DISTINCT pt2.tag_id, t2.name FROM    
(SELECT pt1.post_id
    FROM posts_tags pt1
    INNER JOIN tags t1
        ON pt1.tag_id = t1.id
    WHERE t1.name in ('tag1','tag2','tag3')
    GROUP BY pt1.post_id
    HAVING COUNT(DISTINCT t1.id) = 3) MatchingPosts
INNER JOIN posts_tags pt2 ON (MatchingPosts.post_id = pt2.post_id)
INNER JOIN tags t2 ON (pt2.tag_id = t2.id)
WHERE t2.name NOT IN ('tag1','tag2','tag3');

Answer 1

好吧，这是我能想到的最好的凌晨 4:30:

SELECT distinct tag_id FROM
    (SELECT pt1.post_id FROM pt1
    INNER JOIN tags t1 ON (pt1.tag_id = t1.id)
    WHERE t1.id IN (1, 2)
    GROUP BY pt1.post_id
    HAVING COUNT(DISTINCT t1.id) = 2) MatchingPosts
INNER JOIN pt2 ON (MatchingPosts.post_id = pt2.post_id)
WHERE (pt2.tag_id NOT IN (1, 2))

(1, 2) 是您要查找的标签，当然，计数必须与您用于过滤的标签数量相匹配。

这是一个 example (注意我稍微改变了数据)