MySQL 5.7.12中如何正确筛选符合购/未购条件的客户ID

表结构与数据 #

category_sales表

brand_sales表

product_sales表

当前使用的SQL查询 #

SELECT id
FROM customers
WHERE
id IN (SELECT customer_id FROM category_sales WHERE category_id IN (...))
AND id NOT IN (SELECT customer_id FROM brand_sales WHERE brand_id IN (...))
AND id NOT IN (SELECT customer_id FROM product_sales WHERE product_id IN (...))

问题描述 #

需要获取满足以下条件的客户ID列表：购买了指定分类的商品，且未购买指定品牌和指定商品。但上述查询未达到预期效果，结果中仍包含购买了需排除品牌或商品的客户。

已尝试以下方法但均无效：

• 将三个销售表与customers表进行连接查询
• 使用EXISTS和NOT EXISTS替代WHERE条件中的IN/NOT IN
• 将条件中的AND替换为OR

使用的MySQL版本为5.7.12，无法使用CTE，请问问题出在哪里？

问题根源排查 #

1. NOT IN的NULL值陷阱

如果brand_sales或product_sales的customer_id字段存在NULL值，NOT IN逻辑会直接失效。SQL中任何值与NULL比较的结果都是UNKNOWN，只要子查询返回的结果包含NULL，id NOT IN (...)的条件就会判定为UNKNOWN，最终被WHERE过滤，导致本该排除的客户没有被过滤。

2. 排除条件的子查询逻辑错误

单独执行SELECT customer_id FROM brand_sales WHERE brand_id IN (...)和SELECT customer_id FROM product_sales WHERE product_id IN (...)，检查是否真的包含了需要排除的客户ID。如果参数填错（比如品牌/商品ID写错），子查询不会返回目标客户，NOT IN自然无法过滤。

3. NOT EXISTS的写法错误

如果你之前尝试过NOT EXISTS但无效，大概率是关联条件写错了——比如没有将customers.id和销售表的customer_id关联，导致子查询没有正确匹配客户。

修正后的SQL方案 #

方案1：使用NOT EXISTS（推荐，不受NULL影响）

SELECT c.id
FROM customers c
-- 确保客户购买过指定分类
WHERE EXISTS (
    SELECT 1 
    FROM category_sales cs 
    WHERE cs.customer_id = c.id 
      AND cs.category_id IN (...) -- 替换为你的指定分类ID
)
-- 确保客户从未购买过指定品牌
AND NOT EXISTS (
    SELECT 1 
    FROM brand_sales bs 
    WHERE bs.customer_id = c.id 
      AND bs.brand_id IN (...) -- 替换为你的排除品牌ID
)
-- 确保客户从未购买过指定商品
AND NOT EXISTS (
    SELECT 1 
    FROM product_sales ps 
    WHERE ps.customer_id = c.id 
      AND ps.product_id IN (...) -- 替换为你的排除商品ID
)

方案2：左连接过滤（适合直观理解）

SELECT DISTINCT c.id
FROM customers c
-- 关联指定分类的购买记录
JOIN category_sales cs 
  ON c.id = cs.customer_id 
  AND cs.category_id IN (...) -- 指定分类ID
-- 左连接需排除的品牌记录
LEFT JOIN brand_sales bs 
  ON c.id = bs.customer_id 
  AND bs.brand_id IN (...) -- 排除品牌ID
-- 左连接需排除的商品记录
LEFT JOIN product_sales ps 
  ON c.id = ps.customer_id 
  AND ps.product_id IN (...) -- 排除商品ID
-- 过滤掉有排除品牌/商品购买记录的客户
WHERE bs.customer_id IS NULL 
  AND ps.customer_id IS NULL

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