mysql 多条件匹配,MySQL在与多个条件匹配的一对多关系中苦于查询

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.

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?!

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

解决方案

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

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