Oracle SQL开发需求：查找Item2缺失的Item1已配置仓库记录

云上老周

2026-6-20

问题描述

需编写Oracle环境下的SQL语句，在同一张表中对比Item1和Item2的仓库配置，找出Item2未配置但Item1已配置的仓库记录，要求结果中Item列显示为Item2。

现有表数据

Warehouse	Item
Alabama	Item1
Arizona	Item1
California	Item1
Colorado	Item1
Georgia	Item1
Illinois	Item1
Maine	Item1
Montana	Item1
Texas	Item1
Alabama	Item2
Arizona	Item2
Colorado	Item2
Illinois	Item2
Texas	Item2

期望输出

Warehouse	Item
California	Item2
Georgia	Item2
Maine	Item2
Montana	Item2

自行编写的错误SQL

SELECT
  t1.Item, t1.Warehouse
FROM table t1
WHERE t1.Item = 'Item1'
and NOT EXISTS (SELECT 1 
                    FROM table t2
                   WHERE t2.Item1 <> t1.Item2
                     AND t2.Warehouse = t1.Warehouse
                     and t2.Item = ('Item2'));

正确SQL语句

原SQL的问题是引用了表中不存在的t2.Item1和t1.Item2字段，表中仅存在Item字段。以下是两种可行的正确写法：

方法1：NOT EXISTS 实现

先筛选Item1的所有仓库，再判断这些仓库没有对应Item2的记录，最后将Item字段固定为'Item2'：

SELECT
  'Item2' AS Item,
  t1.Warehouse
FROM your_table t1
WHERE t1.Item = 'Item1'
AND NOT EXISTS (
    SELECT 1 
    FROM your_table t2
    WHERE t2.Warehouse = t1.Warehouse
      AND t2.Item = 'Item2'
);

方法2：LEFT JOIN 实现

通过左连接关联Item1和Item2的仓库记录，筛选出Item2匹配为空的结果：

SELECT
  'Item2' AS Item,
  t1.Warehouse
FROM your_table t1
LEFT JOIN your_table t2
  ON t1.Warehouse = t2.Warehouse
  AND t2.Item = 'Item2'
WHERE t1.Item = 'Item1'
  AND t2.Warehouse IS NULL;