MySQL 多个 WHERE IN 与 AND 运算符在同一列上

Question

在 MySQL 方面需要一些帮助。

我正在建立网上商店，但我在使用同一列上的多个 WHERE IN 子句过滤搜索结果时遇到问题。

我有表 shopitem、filter、filter_option 和 shopitem_option。

shopitem(带有名称、描述、价格等的商品)

id
title
price

过滤器(主要过滤器类别，如颜色、品牌、性别、尺码等)

id
name

filter_option(尺寸(S、M、L)、颜色(黑色、白色)等过滤器选项)

id
name
filter_id (filter.id)

shopitem_option(商品与选项之间的联系)

id
shopitem_id (shopitem.id)
filter_id (filter.id)
filter_option_id (filter_option.id)

我有各种不同尺寸、颜色、性别等的耐克气垫鞋......

所以在 shopitem_option 表中我有以下内容:

id  |  shopitem_id  |  filter_id  |  filter_option_id
-----------------------------------------------------
1   |  Nike Air     |  Color      |  black
2   |  Nike Air     |  Color      |  white
3   |  Nike Air     |  Size       |  40
4   |  Nike Air     |  Size       |  41
5   |  Nike Air     |  Size       |  42
6   |  Nike Air     |  Gender     |  man

当我创建 MySQL 查询时，它需要像这样(在纯 SQL 查询中而不是 'black'、'men' 等。有 filter_option.id 字段的 ID)

SELECT shopitem.*
FROM shopitem
LEFT JOIN shopitem_option.shopitem_id = shopitem.id
WHERE
    shopitem_option.filter_option_id IN (black) AND 
    shopitem_option.filter_option_id IN (41,42) AND 
    shopitem_option.filter_option_id IN (man)

但这行不通。如果我只搜索 40 号，或只搜索 黑色，它将返回所有 40 号鞋或所有黑色鞋。但是，如果我结合过滤器来搜索颜色为黑色且尺寸为 40 的项目，它会返回空查询结果。

因此，基本上用户可以搜索鞋子WHERE 颜色(黑色OR 白色) AND 尺码(41 OR 42) AND 性别 (男)。我怎样才能做到这一点？

Answer 1

IN ... AND IN ... 不能在这里工作，因为你告诉数据库选择 shopitem_option 其中 filter_option_id 应该是 black 和 41 用于同一条记录。像这样使用 EXISTS:

SELECT *
  FROM shopitem i
 WHERE EXISTS (SELECT 1 FROM shopitem_option o WHERE i.id = o.shopitem_id AND o.filter_option_id IN (black))
   AND EXISTS (SELECT 1 FROM shopitem_option o WHERE i.id = o.shopitem_id AND o.filter_option_id IN (41, 42))
   AND EXISTS (SELECT 1 FROM shopitem_option o WHERE i.id = o.shopitem_id AND o.filter_option_id IN (man))