mysql - 从最后一个 WHERE 条件获取结果

Question

我有以下数据库设计。

产品

+----+----------------------------------+
| id | name                             |
+----+----------------------------------+
| 1  | Product 1, Intel, Celeron, N3350 |
| 2  | Product 2, Intel, Celeron, N3350 |
| 3  | Product 3, Intel, i5, 8250U      |
| 4  | Product 4, Intel, i9, 8950HK     |
| 5  | Product 5, Intel, i9, 8950HK     |
+----+----------------------------------+

属性

+----+--------------+
| id | name         |
+----+--------------+
| 11 | Manufacturer |
| 22 | Type         |
| 33 | Model        |
+----+--------------+

ATTRIBUTE_VALUES

+-----+---------+
| id  | value   |
+-----+---------+
| 111 | Intel   |
| 222 | Celeron |
| 333 | i5      |
| 444 | i9      |
| 555 | N3350   |
| 666 | 8250U   |
| 777 | 8950HK  |
+-----+---------+

最后，链接所有这些的表格:

PRODUCT_ATTRIBUTES_VALUES

+------------+--------------+--------------------+
| product_id | attribute_id | attribute_value_id |
+------------+--------------+--------------------+
| 1          | 11           | 111                |
| 1          | 22           | 222                |
| 1          | 33           | 555                |
| 2          | 11           | 111                |
| 2          | 22           | 222                |
| 2          | 33           | 555                |
| 3          | 11           | 111                |
| 3          | 22           | 333                |
| 3          | 33           | 666                |
| 4          | 11           | 111                |
| 4          | 22           | 444                |
| 4          | 33           | 777                |
| 5          | 11           | 111                |
| 5          | 22           | 444                |
| 5          | 33           | 777                |
+------------+--------------+--------------------+

所有这些都是关于过滤器产品。

我想返回每个属性过滤器的结果。

如果我过滤:

• 属性 'Intel' => 它应该返回所有 5 个结果。
• 键入“Celeron”=> 它应该返回 2 个结果(仅 Celeron)

SQL-FIDDLE

 SELECT products.id, products.name FROM `products` LEFT JOIN
 `product_attributes_values` ON
 `product_attributes_values`.`product_id`=`products`.`id` LEFT JOIN
 `attributes` ON
 `attributes`.`id`=`product_attributes_values`.`attribute_id` LEFT JOIN
 `attribute_values` ON
 `attribute_values`.`id`=`product_attributes_values`.`attribute_value_id`
 WHERE ((attributes.id = 11 AND `attribute_values`.`id` IN (111)) OR 
 (`attributes`.`id` = 22 AND `attribute_values`.`id` IN (222)) ) GROUP
 BY `products`.`id` LIMIT 10

Answer 1

试试这个

SELECT
    products.id,
    products.name
FROM
    `products`
    LEFT JOIN `product_attributes_values`
        ON `product_attributes_values`.`product_id`=`products`.`id`
    LEFT JOIN `attributes`
        ON `attributes`.`id`=`product_attributes_values`.`attribute_id`
    LEFT JOIN `attribute_values`
        ON `attribute_values`.`id`=`product_attributes_values`.`attribute_value_id`
WHERE
    attributes.id = 11
    OR attributes.id = 22
GROUP BY
    products.id,
    products.name
HAVING
    MAX(CASE WHEN attributes.id = 11 THEN attribute_values.id ELSE NULL END) IN (111)
    AND MAX(CASE WHEN attributes.id = 22 THEN attribute_values.id ELSE NULL END) IN (222)
LIMIT
    10

如果你知道你所有的id，那么你就不需要所有这些连接

SELECT
    p.id,
    p.name
FROM
    `products` as p
    LEFT JOIN `product_attributes_values` as pav1
        ON  p.id = pav1.product_id
            and pav1.attribute_id = 11
    LEFT JOIN `product_attributes_values` as pav2
        ON  p.id = pav2.product_id
            and pav2.attribute_id = 22
WHERE
    pav1.attribute_value_id = 111
    AND pav2.attribute_value_id = 222