从联接表过滤

本文介绍了从联接表过滤的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在使用棘手的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的解决方案:仅获取与联接表(SQL)中的所有条目匹配的行 如果有人有更好的解决方案，请发表:)

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 );