MySQL查询仅关联禁用分类的商品

原SQL的问题 #

你提供的SQL只能筛选出至少关联了一个禁用分类的商品，但无法排除那些同时关联了启用分类的商品，所以不符合“仅关联禁用分类”的需求。

几种可行的解决方案 #

方案一：使用NOT EXISTS排除关联启用分类的商品

逻辑直观，先确保商品有禁用分类关联，再排除存在启用分类关联的商品：

SELECT p.id
FROM products p
WHERE EXISTS (
    SELECT 1
    FROM products_categories pc
    JOIN categories c ON pc.category_id = c.id
    WHERE pc.product_id = p.id
      AND c.status = 0
)
AND NOT EXISTS (
    SELECT 1
    FROM products_categories pc
    JOIN categories c ON pc.category_id = c.id
    WHERE pc.product_id = p.id
      AND c.status = 1
);

方案二：分组后通过聚合函数判断

利用MAX()函数特性（status只有0和1两种值），分组后如果最大status为0，说明该商品关联的所有分类都是禁用状态：

SELECT pc.product_id AS id
FROM products_categories pc
JOIN categories c ON pc.category_id = c.id
GROUP BY pc.product_id
HAVING MAX(c.status) = 0;

注：如果需要包含无任何分类关联的商品，可通过左连接products表并处理NULL值调整。

方案三：左连接排除启用分类关联

通过左连接启用分类的关联记录，筛选出左连接结果为空的商品（即无启用分类关联），同时确保有禁用分类关联：

SELECT DISTINCT p.id
FROM products p
JOIN products_categories pc ON p.id = pc.product_id
JOIN categories c ON pc.category_id = c.id AND c.status = 0
LEFT JOIN products_categories pc2 ON p.id = pc2.product_id
LEFT JOIN categories c2 ON pc2.category_id = c2.id AND c2.status = 1
WHERE c2.id IS NULL;

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