SQL查询组，其中一列缺少特定数据

Question

我有一个由订单信息组成的Oracle SQL数据库，例如

<表类> ORDER_ID 时间戳 操作 ORDER_STATUS … tbody><<tr>100:00:01插入新…100:00:05更新部分填写…200:00:07更新部分填写…100:00:08取消填写…300:00:08插入新…

您可以使用条件聚合来检查某些状态是否与其他状态同时存在。

select order_id
from sample
where operation in ('INSERT', 'UPDATE', 'CANCEL', 'OTHER_STATUS_TO_CHECK_TOGETHER_WITH_INSERT')
group by order_id
having count(case operation when 'INSERT' then 1 end) = 0

对于示例数据，它返回

ORDER_ID

看看下面是否有帮助:

Select Order_Id
from t
where Operation in ('UPDATE','CANCEL')
and not exists (
select * from t t2
where t2.Order_Id = t.Order_Id and t2.Operation = 'INSERT'
);

从Oracle 12开始，您可以使用MATCH_RECOGNIZE执行逐行模式匹配，以确定UPDATE或CANCEL操作之前是否有INSERT操作:

SELECT order_id
FROM   order_book
MATCH_RECOGNIZE(
PARTITION BY order_id
ORDER     BY timestamp
MEASURES
COUNT(insert_op.operation) AS has_insert
PATTERN ( insert_op? (update_op|cancel_op)+ )
DEFINE
insert_op AS operation = 'INSERT',
update_op AS operation = 'UPDATE',
cancel_op AS operation = 'CANCEL'
)
WHERE has_insert = 0;

对于样本数据:

CREATE TABLE order_book (ORDER_ID, TIMESTAMP, OPERATION, ORDER_STATUS ) AS
SELECT 1, '00:00:01', 'INSERT', 'New'              FROM DUAL UNION ALL
SELECT 1, '00:00:05', 'UPDATE', 'Partially Filled' FROM DUAL UNION ALL
SELECT 2, '00:00:07', 'UPDATE', 'Partially Filled' FROM DUAL UNION ALL
SELECT 1, '00:00:08', 'CANCEL', 'Filled'           FROM DUAL UNION ALL
SELECT 3, '00:00:08', 'INSERT', 'NEW'              FROM DUAL;

输出:

ORDER_ID