从联接表过滤 [英] Filtering from join-table

问题描述

我在使用棘手的SQL查询时遇到了一些麻烦.

I'm having some trouble with a tricky SQL-query.

在我的MySQL数据库中，有表主题，标签和tags_topics可以将它们联接起来. 我想获取共享相同指定标签的主题.例如，假设我有3个ID为1、2和3的标签，我想获取所有与标签1、2和3相关联的主题.主题可以具有其他标签，但必须具有所有指定的标签.

In my MySQL database there is the tables topics, tags and tags_topics to join them. I want to fetch topics that share the same specified tags. For example, let's say i have 3 tags with ids 1, 2 and 3, i want to fetch all topics that have tag 1, 2 and 3 associated to them. The topics can have other tags, but must have all of the specified tags.

帮我思考一下plz xD

Help me think plz xD

在以下问题中找到了使用GROUP BY的解决方案:

Found a solution using GROUP BY in this question: Fetching only rows that match all entries in a joined table (SQL) If anyone have a more elegant solution, please post :)

推荐答案

JOIN解决方案:

JOIN solution:

SELECT t.*
FROM topics t
 JOIN tags_topics t1 ON (t.id = t1.topicId AND t1.tagId = 1)
 JOIN tags_topics t2 ON (t.id = t2.topicId AND t2.tagId = 2)
 JOIN tags_topics t3 ON (t.id = t3.topicId AND t3.tagId = 3)

GROUP BY解决方案:

GROUP BY solution:

请注意，除非使用MySQL或SQLite，否则您需要在GROUP BY子句中列出所有t.*列.

Note that you need to list all t.* columns in the GROUP BY clause, unless you use MySQL or SQLite.

SELECT t.*
FROM topics t JOIN tags_topics tt 
  ON (t.id = tt.topicId AND tt.tagId IN (1,2,3))
GROUP BY t.id, ...
HAVING COUNT(*) = 3;

子查询解决方案:

SELECT t.*
FROM topics t
WHERE t.id = ANY (SELECT topicId FROM tags_topics tt WHERE tt.tagId = 1)
  AND t.id = ANY (SELECT topicId FROM tags_topics tt WHERE tt.tagId = 2)
  AND t.id = ANY (SELECT topicId FROM tags_topics tt WHERE tt.tagId = 3);

修改的GROUP BY解决方案:

Modified GROUP BY solution:

通过隔离子查询中的搜索来简化GROUP BY子句.

Simplifies GROUP BY clause by isolating search in a subquery.

SELECT t.*
FROM topics t
WHERE t.id IN (
  SELECT tt.topicId FROM tags_topics tt 
  WHERE tt.tagId IN (1,2,3))
  GROUP BY tt.id HAVING COUNT(*) = 3
);

这篇关于从联接表过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！