高级webshop过滤w/ MySQL

Question

我正在用MySQL和PHP制作一个带有数据库设计similar to this的网络商店(见图)。

我对不同规格的多价格商品没有问题，就像上面提到的问题一样。

这将是一个计算机网络商店，所以我没有必要为每个产品制定一个规格。我可以把他们归为前几个人。智能手机笔记本电脑。并为每个类别添加规范。

智能手机可以有:品牌，处理器类型，屏幕大小，颜色，电池，价格等。

笔记本电脑:处理器，显卡，品牌，价格，尺寸，.

我也认为没有必要使用[combinations]表，因为在我看来，这将是不必要的存储。

我的问题是过滤产品。我可以很容易地选择其中的所有，或仅仅一个，或例如。选择所有白色手机或所有白色或金色手机，但我不能选择所有的白色手机有一个8MP前摄像头。

表结构：

[products]：id，名称，category_id，描述

[categories]：id，名称

[specifications]：id，category_id，名称

[spec_values]：id，product_id，spec_id，value

下面的SQL返回所有不同的产品，这些产品都是金色的，或者有一个8mp前置摄像头。

SELECT products.id, products.name, spec_values.value AS Filter
FROM products, categories, specifications, `spec_values` 
WHERE products.category_id = categories.id 
    AND spec_values.product_id = products.id 
    AND ((specifications.name = 'Color' AND spec_values.value = 'Gold') 
        OR (specifications.name = 'Front Camera' AND spec_values.value = '8MP')) 
GROUP BY products.id;

我希望在它们之间有一个逻辑AND，而不是一个OR，但是简单地更改它就什么也不会返回，因为它不能在单个行中实现，因为我的规范名称和值存储在多个行上。

一种解决方案是为每个过滤器提供SQL CREATE TEMPORARY TABLE，但我认为这需要大量不必要的服务器端工作。

另一种方法是获取所有不不同的值，并使用PHP迭代它们，并检查它们是否都存在；如果是，则在页面上显示它们，但是，这个值可能也很混乱。

有没有更好的解决方案，我没有注意到，因为我在SQL方面的经验？提前感谢您的帮助。祝你有愉快的一天！

Answer 1

您可以为此使用聚合。包含要检查的HAVING子句，计数是否与筛选器的数目匹配。除非您已经两次(或更多)插入了确切的规格值，否则您将知道满足该条件的产品必须对所有规格值进行筛选。如果没有，则缺少一行(或多行)，即一个(或多个)指定值。

SELECT products.id,
       products.name
       FROM products
            INNER JOIN spec_values
                       ON spec_values.product_id = products.id 
            INNER JOIN specifications
                       ON specifications.id = spec_values.spec_id
       WHERE specifications.name = 'Color' AND spec_values.value = 'Gold'
              OR specifications.name = 'Front Camera' AND spec_values.value = '8MP'
       GROUP BY products.id,
                products.name
       HAVING count(*) = 2;

顺便说一句，为了提高可读性，在FROM子句中的逗号分隔列表上使用显式联接语法是明智的。