SQL如何筛选同时属于两个指定分类且排除某分类的产品

原SQL错误原因 #

你写的SQL存在两处逻辑缺陷：

• WHERE是行级过滤规则，只能校验单条关联记录是否属于1/2且不为3，无法保证同一个产品同时关联了分类1和分类2
• 行级过滤剔除的只是分类为3的关联行，无法排除产品本身其他关联记录存在分类3的情况。比如产品3同时关联1、2、3，WHERE会保留它的1、2关联行，分组后会被误判为符合条件的结果

正确实现SQL #

以下方案基于分组聚合判断，完全匹配你的业务规则，测试数据下返回结果正好是product_id为1、2的产品：

SELECT p.product_id, p.product_name 
FROM products p
INNER JOIN product_category_relations r ON r.relations_product_id = p.product_id
GROUP BY p.product_id, p.product_name
HAVING 
  -- 确认关联了分类1
  COUNT(DISTINCT CASE WHEN r.relations_category_id = 1 THEN 1 END) = 1
  -- 确认关联了分类2
  AND COUNT(DISTINCT CASE WHEN r.relations_category_id = 2 THEN 1 END) = 1
  -- 确认未关联分类3
  AND COUNT(DISTINCT CASE WHEN r.relations_category_id = 3 THEN 1 END) = 0;

可选高性能方案：EXISTS写法 #

如果产品分类关联表建立了relations_product_id + relations_category_id的联合索引，该写法性能更优，可读性也更高：

SELECT p.product_id, p.product_name
FROM products p
WHERE
  EXISTS (SELECT 1 FROM product_category_relations r WHERE r.relations_product_id = p.product_id AND r.relations_category_id = 1)
  AND EXISTS (SELECT 1 FROM product_category_relations r WHERE r.relations_product_id = p.product_id AND r.relations_category_id = 2)
  AND NOT EXISTS (SELECT 1 FROM product_category_relations r WHERE r.relations_product_id = p.product_id AND r.relations_category_id = 3);

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