MySQL在与多个条件匹配的一对多关系中难以进行查询 [英] MySQL struggling with query in one to many relationship matching multiple conditions

问题描述

我有两个表，大致列出如下:

I have two tables which are set out roughly as follows:

products            product_attributes
==================  ========================================
| id | name      |  | id | product_id | attribute | value  |
==================  ========================================
| 1  | product 1 |  | 1  | 1          | size      | big    |
| 2  | product 2 |  | 2  | 1          | colour    | red    |
| 3  | product 3 |  | 3  | 2          | size      | medium |
| 3  | product 3 |  | 4  | 2          | age_range | 3-5    |
| .. | ...       |  | 5  | 2          | colour    | blue   |
==================  | 6  | 3          | size      | small  |
                    | .. | ...        | ...       | ...    |
                    ========================================

产品的属性数量可能无限，这就是为什么将它们保存在单独的表中的原因.

There are potentially an infinite amount of attributes for a product which is why they are kept in a separate table.

我希望能够提取出与多个(也是无限的)属性条件相匹配的独特产品，但我想不通过使用OR条件然后再进行某种计数来检查所有属性是否匹配的方法，就可以做到这一点.我很确定这不是最好的方法，所以希望有人能提供帮助?！

I want to be able to pull out distinct products which match MULTIPLE (also infinite) attribute conditions but I cant think how to do it without maybe using an OR condition and then some sort of count to check all of the attributes were matched. Im fairly sure this isnt the best way so hopefully someone can help?!

例如，查找尺寸为'medium'和颜色='blue'的产品(这与上面示例中的产品2相匹配).

For example find products which have size = 'medium' and colour = 'blue' (this would match product 2 in the example above).

推荐答案

这是用COUNT建议的方式可能是MySQL中最简单的方法

The way you suggest with the COUNT is probably the easiest in MySQL

SELECT product_id
FROM product_attributes pa
WHERE (attribute='size' and value='medium')
OR (attribute='colour' and value='blue')
GROUP BY product_id
HAVING COUNT(DISTINCT CONCAT(attribute,value) ) = 2

链接文章中还有另一种使用双精度NOT EXISTS的方法，但是由于MySQL不支持CTE，因此非常麻烦.

There is another approach with double NOT EXISTS in the linked article but as MySQL does not support CTEs that would be quite cumbersome.

这篇关于MySQL在与多个条件匹配的一对多关系中难以进行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！