如何编写SQL查询找出未拥有全部指定item的目标ID？

嘿，这个需求我经常碰到，给你分享几个好用的SQL解法，总能找到适合你场景的：

核心思路是统计每个用户实际拥有的不重复item数量，只要数量小于要求的总数（这里是3），就说明该用户有缺失。

SELECT ID
FROM Table2
GROUP BY ID
HAVING COUNT(DISTINCT item_ID) < 3;

如果你的数据里每个用户的item_ID不会重复（比如一条记录对应一个用户一个唯一item），那可以去掉DISTINCT，直接用COUNT(item_ID)，效率会更高一点。

要是以后要求的item列表有变动（比如加item_4），这种方法改起来最方便。先生成「所有用户 + 所有要求item」的全量组合，再和现有数据做左连接，找不到匹配的就是缺失的。

-- 先定义必须拥有的item列表
WITH required_items AS (
    SELECT 'item_1' AS item_ID UNION ALL
    SELECT 'item_2' UNION ALL
    SELECT 'item_3'
),
-- 生成每个用户应该有的所有item组合
user_required_pairs AS (
    SELECT DISTINCT t.ID, ri.item_ID
    FROM Table2 t
    CROSS JOIN required_items ri
)
-- 找出全量组合中不存在于Table2的记录，对应的ID就是有缺失的用户
SELECT DISTINCT urp.ID
FROM user_required_pairs urp
LEFT JOIN Table2 t2 
    ON urp.ID = t2.ID AND urp.item_ID = t2.item_ID
WHERE t2.item_ID IS NULL;

要是你想顺便看看每个用户具体缺了哪个item，把最后一行的SELECT DISTINCT urp.ID改成SELECT urp.ID, urp.item_ID就行，一目了然。

如果要求的item数量不多，这种写法逻辑最容易理解——直接检查用户是否缺少某一个item，只要缺任意一个就筛选出来。

SELECT DISTINCT ID
FROM Table2 t
WHERE 
    NOT EXISTS (SELECT 1 FROM Table2 WHERE ID = t.ID AND item_ID = 'item_1')
    OR NOT EXISTS (SELECT 1 FROM Table2 WHERE ID = t.ID AND item_ID = 'item_2')
    OR NOT EXISTS (SELECT 1 FROM Table2 WHERE ID = t.ID AND item_ID = 'item_3');

缺点是如果以后要加新的item，就得再多加一个OR NOT EXISTS的条件，适合item列表固定且数量少的场景。

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