如何筛选分组查询结果以同时匹配多个属性值？

你现在的写法之所以达不到预期，本质是IN 自带OR匹配逻辑，分组后根本没校验匹配到的选项值总数是不是和你传入的筛选条件总数一致——只要产品关联到的记录里有6或者10任意一个值，就会被捞出来，完全不管是不是两个都满足。要实现“产品/变体必须同时凑齐所有传入的option_value筛选值”的逻辑，是非常经典的关系除法类查询，有两种生产环境常用的实现方案可以直接用：

方案1：分组计数校验（和你原有SQL结构兼容度最高） #

实现逻辑 #

• 固定传入的筛选option_value_id集合，计算集合的元素总个数
• 按产品/变体维度分组时，统计该分组下匹配到的去重后的option_value_id数量
• 只有分组的匹配数量等于筛选集合的总个数，才算满足全条件要求

产品维度的筛选写法（只要产品下存在至少一个变体凑齐所有筛选值，就返回该产品） #

SELECT p.product_id, p.title, p.description 
FROM product_variants pv
JOIN products p ON pv.product_id = p.product_id
-- 注意对齐你表结构的实际关联字段，之前的写法字段名不匹配
JOIN variant_values vv ON pv.variant_id = vv.variant_id
WHERE vv.option_value_id IN (6, 10) -- 替换为动态传入的筛选值数组
GROUP BY p.product_id, p.title, p.description
-- 核心校验逻辑：匹配到的去重选项值数量 = 传入筛选值的总个数
HAVING COUNT(DISTINCT vv.option_value_id) = 2; -- 2 要和IN数组的元素长度保持一致

变体维度的筛选写法（必须是同一个变体同时拥有所有筛选值，避免产品下两个不同变体凑齐条件的误匹配） #

SELECT DISTINCT p.product_id, p.title, p.description, pv.variant_id, pv.price
FROM product_variants pv
JOIN products p ON pv.product_id = p.product_id
JOIN variant_values vv ON pv.variant_id = vv.variant_id
WHERE vv.option_value_id IN (6, 10)
GROUP BY pv.variant_id, p.product_id, p.title, p.description, pv.price
HAVING COUNT(DISTINCT vv.option_value_id) = 2;

这个写法的适配性非常强，程序里只要动态替换IN里的筛选值数组、同步更新HAVING里的计数（即数组长度），就能支持任意数量的筛选条件，和主流电商的筛选逻辑完全对齐。注意一定要加DISTINCT做去重，避免重复关联同一个选项值导致计数虚高。

方案2：逐条件存在校验（大数据量下性能更优） #

实现逻辑 #

对每一个筛选的option_value_id，单独校验当前的产品/变体是否存在对应的关联记录，所有校验全部通过才算命中。

产品维度的筛选写法 #

SELECT p.product_id, p.title, p.description
FROM products p
WHERE 
  -- 逐个校验是否存在对应的选项值关联
  EXISTS (
    SELECT 1 FROM product_variants pv
    JOIN variant_values vv ON pv.variant_id = vv.variant_id
    WHERE pv.product_id = p.product_id AND vv.option_value_id = 6
  )
  AND EXISTS (
    SELECT 1 FROM product_variants pv
    JOIN variant_values vv ON pv.variant_id = vv.variant_id
    WHERE pv.product_id = p.product_id AND vv.option_value_id = 10
  );

变体维度的筛选写法 #

SELECT DISTINCT p.product_id, p.title, p.description, pv.variant_id, pv.price
FROM product_variants pv
JOIN products p ON pv.product_id = p.product_id
WHERE 
  EXISTS (
    SELECT 1 FROM variant_values vv 
    WHERE vv.variant_id = pv.variant_id AND vv.option_value_id = 6
  )
  AND EXISTS (
    SELECT 1 FROM variant_values vv 
    WHERE vv.variant_id = pv.variant_id AND vv.option_value_id = 10
  );

如果在variant_values表的(variant_id, option_value_id)字段上建了联合索引，每个EXISTS校验都会走索引快速查询，比全量分组计数的性能高很多，适合数据量大、筛选条件多的场景。

常见注意事项 #

• 严格对齐实际表结构的字段名，避免之前出现的关联字段名不匹配的问题
• 如果业务允许同一个变体关联同一选项下的多个选项值（比如同时选了红色和绿色），不需要额外校验；如果是严格的单选属性，需要额外在WHERE里补充option维度的过滤，避免出现逻辑错配
• 产品维度返回结果时，不要把变体级的字段混在SELECT列表里，否则会出现同一个产品重复返回的问题

内容的提问来源于stack exchange，提问作者Omar Abdelhady