如何用Oracle SQL查询缺失INSERT操作的ORDER_ID

我有一个存储订单簿信息的Oracle SQL数据库，示例数据如下：

需要识别出所有缺失OPERATION为'INSERT'的订单（即同一ORDER_ID下仅存在'UPDATE'或'CANCEL'操作，无'INSERT'操作），比如示例中的ORDER_ID=2。希望通过直接SQL查询实现高效分析。

解决方案 #

方案1：NOT EXISTS子查询（推荐，性能优异） #

当ORDER_ID和OPERATION有联合索引时，该方案执行效率最高：

SELECT DISTINCT ORDER_ID
FROM your_table_name t1
WHERE NOT EXISTS (
    SELECT 1
    FROM your_table_name t2
    WHERE t2.ORDER_ID = t1.ORDER_ID
      AND t2.OPERATION = 'INSERT'
);

逻辑：外层遍历所有订单ID，内层子查询检查该ID是否存在INSERT操作，无INSERT记录的ID会被筛选出来，DISTINCT确保每个ID仅返回一次。

方案2：GROUP BY + HAVING子句 #

通过分组统计筛选无INSERT操作的订单：

SELECT ORDER_ID
FROM your_table_name
GROUP BY ORDER_ID
HAVING COUNT(CASE WHEN OPERATION = 'INSERT' THEN 1 END) = 0;

或等价写法：

SELECT ORDER_ID
FROM your_table_name
GROUP BY ORDER_ID
HAVING MAX(CASE WHEN OPERATION = 'INSERT' THEN 1 ELSE 0 END) = 0;

逻辑：按ORDER_ID分组后，统计每组中INSERT操作的数量（或判断是否存在INSERT），数量为0的分组即为目标订单。

方案3：MINUS集合操作 #

通过集合差集获取结果：

SELECT ORDER_ID FROM your_table_name
MINUS
SELECT ORDER_ID FROM your_table_name WHERE OPERATION = 'INSERT';

逻辑：先获取所有唯一订单ID，再减去存在INSERT操作的订单ID，剩余结果即为无INSERT的订单。

性能优化建议 #

• 为ORDER_ID和OPERATION创建联合索引：CREATE INDEX idx_order_operation ON your_table_name(ORDER_ID, OPERATION);，可大幅提升查询效率
• 数据量极大时，优先选择NOT EXISTS或MINUS方案，Oracle对这两种写法的执行计划优化更充分

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