MySQL 查询 - 1 列上的多个 WHERE 子句

Question

谁能帮我根据下面的查询建立一个查询。

如您所见，我有一个产品，其规范和某些组是在前端构建的。我知道这个问题，1 列不能同时是 2 个值，但我只需要这 2 个组中的那些产品。

为了说明，product_specification_sid，id 2 3 和 4 是尺寸，de rest 8 ~ 11 是颜色，所以我想选择具有 2 和 3 的产品。

内部加入表格 double 不是一种选择，因为组(大小、颜色)将来可能会有所不同。

SELECT
    products.*,
    categories.*,
    manufacturers.* 
FROM products
INNER JOIN  product_categories ON product_category_pid = product_id
INNER JOIN  categories ON product_category_cid = category_id
INNER JOIN  manufacturers ON product_manufacturer = manufacturer_id
INNER JOIN  product_specifications ON product_specification_pid=product_id
WHERE 
    product_active = 1  
AND 
(
    product_specification_sid in (3)  
AND  
    product_specification_sid in (8,9,6,7,10,11)  
) 
GROUP BY product_id

Answer 1

您可以改用 having 子句。

SELECT
    products.*,
FROM products
INNER JOIN  product_categories ON product_category_pid = product_id
INNER JOIN  categories ON product_category_cid = category_id
INNER JOIN  manufacturers ON product_manufacturer = manufacturer_id
INNER JOIN  product_specifications ON product_specification_pid=product_id
WHERE product_active = 1  
GROUP BY product_id
HAVING COUNT(CASE WHEN product_specification_sid in (3) THEN 1 END) > 0
 AND COUNT(CASE WHEN product_specification_sid in (8,9,6,7,10,11) THEN 1 END) > 0