mysql - 避免在 WHERE 子句中使用多个 SELECT

Question

我正在尝试改进我在商家网站中发现的 SQL 错误。

我有两个表:

------ Table "products" ------
| id_product | product_name |
-----------------------------
|          1 |      T-shirt |
|          2 |     Trousers |
|          3 |  Sweat-shirt |
|          4 |        Socks |

----------- Table "features" -----------
| id_product | feature | feature_value |
----------------------------------------
|          1 |   Color |           Red |
|          1 |    Size |             M |
|          1 |  Fabric |        Cotton |
|          2 |   Color |          Blue |
|          2 |    Size |             S |
|          2 |  Fabric |     Polyester |
|          3 |   Color |           Red |
|          3 |    Size |             L |
|          3 |  Fabric |          Wool |
|          4 |   Color |         White |
|          4 |    Size |             L |
|          4 |  Fabric |        Cotton |

我正在尝试检索具有以下特征的产品:

• 颜色为红色或蓝色
• 大小为M
• 布料是棉

我的查询如下:

SELECT p.id_product
FROM products p
WHERE p.id_product IN (SELECT f.id_product FROM features f WHERE f.feature_value IN ("Red", "Blue"))
    AND p.id_product IN (SELECT f.id_product FROM features f WHERE f.feature_value = "M")
    AND p.id_product IN (SELECT f.id_product FROM features f WHERE f.feature_value = "Cotton")
GROUP BY p.id_product

(当然，实际上，我的表和查询比这复杂得多，我只关注有问题的部分)

如果选择 8 个或更多功能，WHERE 子句中的多个 SELECT 会导致我的整个服务器变慢。有没有办法避免在 WHERE 子句中进行这么多查询？

编辑:例如，这是一个真正的查询:

SELECT p.id_product id_product
FROM ps_product p 
INNER JOIN ps_category_product cp ON p.id_product = cp.id_product 
INNER JOIN ps_category c ON (c.id_category = cp.id_category AND c.nleft >= 6 AND c.nright <= 7 AND c.active = 1)
LEFT JOIN ps_stock_available sa ON (sa.id_product = p.id_product AND sa.id_shop = 1) 
INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)
WHERE 1
AND product_shop.active = 1
AND product_shop.visibility IN ("both", "catalog")
AND p.id_manufacturer IN (5,4)
AND sa.quantity > 0
AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.id_feature_value = 82)
AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.id_feature_value = 37248)
AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.id_feature_value = 181)
AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.id_feature_value = 37821)
AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.id_feature_value = 33907)
AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.id_feature_value = 33902)
AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.id_feature_value = 70)
AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.id_feature_value = 76)
AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.id_feature_value = 291)
AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.id_feature_value = 75)
AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.id_feature_value = 44459)
GROUP BY id_product

Answer 1

您可以使用JOIN 和HAVING 子句:

SELECT p.id_product
FROM products p
JOIN features f
  ON p.id_product = f.id_product
GROUP BY p.id_product
HAVING COUNT(CASE WHEN f.feature_value IN ('Red', 'Blue') THEN 1 END) > 0
   AND COUNT(CASE WHEN f.feature_value = 'M' THEN 1 END) > 0
   AND COUNT(CASE WHEN f.feature_value = 'Cotton' THEN 1 END) > 0;

LiveDemo

---

或者更短(MySQL):

HAVING SUM(f.feature_value IN ('Red', 'Blue')) > 0
   AND SUM(f.feature_value = 'M') > 0
   AND SUM(f.feature_value = 'Cotton') > 0;