如何筛选出购买了items_list中全部商品的MySQL用户？

原查询语句的问题在于，它仅筛选出购买过至少一件指定商品的用户，没有校验用户是否买全了items_list中的所有商品。以下是两种可行的优化方案：

方案1：GROUP BY + HAVING 对比商品数量 #

这种方式简洁直观，通过聚合用户购买的指定商品数量，与items_list的总商品数做对比：

SELECT o.user_id
FROM orders o
JOIN items_list i ON o.item_id = i.item_id
GROUP BY o.user_id
HAVING COUNT(DISTINCT o.item_id) = (SELECT COUNT(DISTINCT item_id) FROM items_list)

逻辑说明： #

• 用JOIN替代IN，只保留与指定商品关联的订单记录
• GROUP BY user_id聚合每个用户的有效订单
• HAVING子句中：
  • COUNT(DISTINCT o.item_id)统计该用户实际购买过的不同指定商品数量（去重是为了排除重复购买同一件商品的情况）
  • 子查询(SELECT COUNT(DISTINCT item_id) FROM items_list)动态获取需要购买的全部商品总数，完全适配items_list条目动态变化的场景
• 两者相等时，说明该用户买全了所有指定商品

方案2：双重NOT EXISTS 关联查询 #

适合数据量较大的场景，利用关联子查询校验用户是否遗漏任何指定商品：

SELECT DISTINCT o.user_id
FROM orders o
WHERE NOT EXISTS (
    -- 检查是否存在某个指定商品是当前用户未购买的
    SELECT 1
    FROM items_list i
    WHERE NOT EXISTS (
        -- 校验当前用户是否购买了该商品
        SELECT 1
        FROM orders o2
        WHERE o2.user_id = o.user_id AND o2.item_id = i.item_id
    )
)

逻辑说明： #

• 外层查询遍历所有有订单的用户
• 内层第一个子查询遍历items_list中的每个商品
• 内层第二个子查询检查当前用户是否购买了该商品
• 如果不存在任何用户未购买的指定商品，则该用户符合条件

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