MySQL选择不同的地方 [英] MySQL select distinct where in all

问题描述

我有一个表格产品和一个表格标签，它们都具有以下字段:

I have a table products and a table tags which both have these fields:

id | name
1  | chair
2  | table

和一个链接表product_tags:

And a linking table product_tags:

id | productId | tagId
1  | 1         | 1
2  | 1         | 2
3  | 2         | 1
4  | 2         | 2
5  | 2         | 3
6  | 2         | 4

产品显示在搜索结果中，如果用户在某些标签上打钩，则可以缩小产品范围.因此，我需要一个查询，该查询返回在product_tags中具有所有选定标签的产品列表.例如，在我上面的表格中，如果用户在标签1、2和3上打了钩，则该查询应仅返回产品2.

Products are displayed in a search result and can be narrowed down if the user ticks some of the tags. Therefore I need to have a query that returns the list of products that have all of the selected tags in product_tags. For example in my tables above if the user had ticked tags 1, 2 and 3 then the query should return product 2 only.

由于此查询中还有很多其他联接，只会使这个问题更加复杂，因此，假设我当前的查询(选择具有至少一个标签的所有产品-这需要更改为具有所有标签")如下:

As there are quite a lot of other joins in this query which would only make this question more complex, let's say that my current query (which selects all products which have at least one of the tags - This needs to be changed to "has all tags") is the following:

SELECT DISTINCT p.id, p.name
FROM products AS p
JOIN product_tags AS pt ON p.id = pt.productId
WHERE pt.tagId IN (tag1, tag2, tag3, ...) //the line that will be removed

我当时想也许在where语句中使用COUNT()来获取从标签列表中为每种产品找到的tagId的数量，并有一条WHERE语句强制其等于输入的标签数.但这可能太过分了.最重要的是执行时间.你怎么认为?预先感谢！

I was thinking maybe using COUNT() in the where statement to have the number of tagId found for each product from the list of tags, and have a WHERE statement forcing it to be equal to the number of tags inputed. But that may be overkill. On top of that execution time is the priority. What do you think? Thanks in advance!

推荐答案

使用HAVING子句查看产品是否具有所有标签.

Use a HAVING clause to see if a product has all tags.

SELECT p.id, p.name
FROM products AS p
JOIN product_tags AS pt ON p.id = pt.productId
WHERE pt.tagId IN (tag1, tag2, tag3)
GROUP BY p.id, p.name
HAVING count(DISTINCT pt.tagId) = 3

这篇关于MySQL选择不同的地方的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！