从联接表过滤 [英] 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
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屋!