php - MySQL通过过滤器选择产品

Question

我需要选择具有特定 value_id 的产品 ID。

表产品:

| product_id | product_name | category_id | active |
| 125        | notebook1    | 3           | 1      |
| 236        | notebook2    | 3           | 1      |

表过滤器:

| product_id | value_id | value_name |
| 125        | 35       | 15" display|
| 125        | 36       | 8GB RAM    |
| 236        | 35       | 15" display|

这个选择工作正常，如果我想通过一个 value_id 选择产品 ID:

SELECT DISTINCT p.product_id FROM products p
LEFT JOIN filters f ON (p.product_id=f.product_id)
WHERE p.active=1 AND p.category_id=3 AND f.value_id=36;

但是当我在 web 上检查更多过滤器时，我需要通过更多值进行选择，问题是当我使用时:

SELECT DISTINCT p.product_id FROM products p
LEFT JOIN filters f ON (p.product_id=f.product_id)
WHERE p.active=1 AND p.category_id=3 AND f.value_id IN(35,36);

它为我提供了具有 15"显示屏或 8GB RAM 的产品，我需要具有 15"显示屏和 8GB RAM 的产品。谢谢。

Answer 1

既然您提到了网络，我假设您正在使用一种程序语言来生成查询。我还将假设您无法控制数据在表中的结构，因此我们必须有一个选项可以在给定的表结构下工作。

下面的选项可以工作，但如果您要处理传入的大量选项，它会变得草率。

为每个选项使用后续连接的选项

SELECT 
    DISTINCT p.product_id 
FROM 
    products p
JOIN filters f1 
    ON p.product_id=f1.product_id
    and f1.value_id = 35
JOIN filters f2 
    ON p.product_id=f2.product_id
    and f2.value_id = 36
WHERE 
    p.active=1 
    AND p.category_id=3
;

如果您在逗号分隔列表中获取过滤器值 ID 并希望以这种方式使用它(提示您的 IN 语句)，您可以采用这种方法。我假设您知道传入的过滤器值的总数。在您的示例中，您有 2 个，因此查询会像这样。

组计数等于使用 IN 语句提供的过滤器

SELECT 
    p.product_id
FROM 
    products p
JOIN filters f 
    ON p.product_id=f.product_id
    AND p.active=1 
    AND p.category_id=3 
    AND f.value_id IN(35,36)
GROUP BY
    p.product_id
HAVING
    COUNT(p.product_id) = 2

在连接条件中放置尽可能多的内容而不是 where 子句将有助于加快查询速度，因为 from 子句在 where 子句之前进行评估。