MySQL使用WHERE IN查询时如何实现匹配所有列表值的筛选需求

解决方案 #

你可以在分组后新增HAVING条件，校验每个分组下不重复的product_id数量等于IN列表的元素总数即可，修改后的SQL如下：

select
    pack_id 
from
    pack_product pp 
where
    pp.product_id in (2,1) 
group by
    pack_id
having count(distinct product_id) = 2

实现逻辑 #

• WHERE子句先过滤出所有符合product_id为1或2的记录，减少后续分组计算的数据量
• 按pack_id分组后，每个分组对应同一个套餐ID下的所有关联商品ID
• count(distinct product_id)统计每个分组下不重复的商品ID数量，当数量等于2时，说明该pack_id同时关联了1和2两个商品，符合需求。

注意事项 #

如果你的pack_product表有唯一约束限制同一个pack_id下不会出现重复的product_id，可以去掉distinct关键词，直接写count(product_id) = 2，执行效率更高。

可选实现方案（仅支持MySQL 8.0.31及以上版本） #

也可以用INTERSECT取两个查询的交集实现，逻辑更直观：

select pack_id from pack_product where product_id = 1
intersect
select pack_id from pack_product where product_id = 2

内容的提问来源于stack exchange，提问作者nassim miled