mysql n:m关系:查找具有几个特定关系的行 [英] mysql n:m relationship: Find rows with several specific relations
问题描述
我有两个SQL表,产品"和标签".它们具有n:m关系,使用第三个表"product_tags".
I have two SQL Tables, 'products' and 'tags'. They have an n:m relationship, using a third table 'product_tags'.
我想使用查询来查找具有多个特定标签的每个产品.例如,找到与标签1、23和54相关的所有产品.
I want to use a query to find every product that has a number of specific tags. For example, find every products that has a relation to the tags 1, 23 and 54.
是否有一种方法可以只执行一个查询?
Is there a way to do this with just one query?
推荐答案
您可以使用此解决方案.这将获取所有包含 ALL 关键字1、23和54的产品:
You can use this solution. This gets all products that contain ALL keywords 1, 23, and 54:
SELECT a.*
FROM products a
INNER JOIN product_tags b ON a.product_id = b.product_id
WHERE b.tag_id IN (1,23,54)
GROUP BY a.product_id
HAVING COUNT(1) = 3
3
是WHERE IN
列表中项目的数量,因此您可以根据要检查的标签数量进行相应调整.
Where 3
is the number of items in your WHERE IN
list, so you can adjust accordingly based on the amount of tags you want to check on.
这篇关于mysql n:m关系:查找具有几个特定关系的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!