Oracle SQL查询：找出物品缺失的指定5个地点记录

要找出所有物品在Austin、Boston、Chicago、Dallas、Houston这5个地点中缺失的记录，核心思路是先生成所有物品与这5个地点的可能组合，再排除掉数据库中已经存在的组合，剩下的就是需要补充的缺失记录。以下是两种可行的SQL写法：

方法1：使用LEFT JOIN筛选缺失记录 #

WITH target_locations AS (
    -- 定义需要检查的5个目标地点
    SELECT 'Austin' AS location FROM dual
    UNION ALL SELECT 'Boston' FROM dual
    UNION ALL SELECT 'Chicago' FROM dual
    UNION ALL SELECT 'Dallas' FROM dual
    UNION ALL SELECT 'Houston' FROM dual
),
all_possible_pairs AS (
    -- 生成所有物品与目标地点的笛卡尔积
    SELECT DISTINCT i.item_id, tl.location
    FROM inventory i
    CROSS JOIN target_locations tl
)
-- 筛选出原表中不存在的物品-地点组合
SELECT app.item_id, app.location
FROM all_possible_pairs app
LEFT JOIN inventory inv
    ON app.item_id = inv.item_id
    AND app.location = inv.location
WHERE inv.item_id IS NULL
ORDER BY app.item_id, app.location;

方法2：使用NOT EXISTS子查询 #

WITH target_locations AS (
    SELECT 'Austin' AS location FROM dual
    UNION ALL SELECT 'Boston' FROM dual
    UNION ALL SELECT 'Chicago' FROM dual
    UNION ALL SELECT 'Dallas' FROM dual
    UNION ALL SELECT 'Houston' FROM dual
)
-- 直接筛选出未在原表中出现的物品-地点组合
SELECT DISTINCT i.item_id, tl.location
FROM inventory i
CROSS JOIN target_locations tl
WHERE NOT EXISTS (
    SELECT 1
    FROM inventory inv
    WHERE inv.item_id = i.item_id
    AND inv.location = tl.location
)
ORDER BY i.item_id, tl.location;

注意事项： #

• 请根据你的实际表名和列名替换示例中的inventory（物品记录表名）、item_id（物品唯一标识列）、location（地点列）。
• 使用DISTINCT是为了避免物品在原表中有多条同地点记录时生成重复的组合。
• 最终结果会按物品ID和地点排序，方便你整理和补充缺失的记录。

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