MySQL 在一对多关系匹配多个条件的查询中遇到困难

发布于 2024-10-05 01:56:28 字数 954 浏览 1 评论 0原文

我有两个表，大致如下：

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  |
                    | .. | ...        | ...       | ...    |
                    ========================================

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

我希望能够提取出与多个（也是无限）属性条件匹配的不同产品，但我无法想象如何在不使用“或”条件的情况下做到这一点，然后使用某种计数来检查所有属性是否匹配。我相当确定这不是最好的方法，所以希望有人可以提供帮助？！

例如，查找尺寸=“中”且颜色=“蓝色”的产品（这将与上例中的产品 2 匹配）。

原文

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).

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

彼岸花似海 2024-10-12 01:56:28

这是一个关系划分问题。

您使用 COUNT 建议的方式可能是 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，因此会很麻烦。麻烦。

This is a relational division problem.

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.

回复收藏 0 原文

梦晓ヶ微光ヅ倾城 2024-10-12 01:56:28

我认为这应该可以解决问题：

SELECT a.product_id, p.name FROM product_attributes AS a LEFT JOIN products p ON (a.product_id=p.id) WHERE (a.attribute="size" AND a.value="medium") OR (a.attribute="colour" AND a.value="blue") GROUP BY a.product_id

I think this should do the trick:

SELECT a.product_id, p.name FROM product_attributes AS a LEFT JOIN products p ON (a.product_id=p.id) WHERE (a.attribute="size" AND a.value="medium") OR (a.attribute="colour" AND a.value="blue") GROUP BY a.product_id

回复收藏 0 原文

~没有更多了~