根据用户在MYSQL中选择的选项获取产品

Question

我希望产品具有IN子句中的所有选项。这是我的SQL：

SELECT 
    p.title, p.price FROM products AS p, product_options AS po 
WHERE 
    p.product_id = po.product_id 
AND 
    po.product_option_id IN (5,1,38,39) 
GROUP BY 
    p.product_id; 

Answer 1

如果你想返回具有所有这些选项的结果，那么你可以使用：

SELECT p.title, p.price 
FROM products AS p 
INNER JOIN product_options AS po 
    ON p.product_id = po.product_id 
WHERE po.product_option_id IN (5,1,38,39) 
GROUP BY p.product_id 
HAVING COUNT(DISTINCT po.product_option_id) = 4; 

见SQL Fiddle with Demo