如何用SQL校验ORDERS表中订单按创建顺序完成的合规性？

我们有一个ORDERS表用于模拟订单场景，要求订单必须按创建顺序完成，不允许后创建的订单比更早创建的订单先完成。

表结构 #

ORDERS表包含以下字段：

id: number
task: varchar
creation: DateTime
completed: DateTime

• 订单创建时，creation字段赋值为创建时间，completed字段为null；
• 订单完成时，completed字段赋值为当前时间。

问题说明 #

需要通过SQL校验所有订单是否符合处理顺序要求：不存在后创建的订单先完成的情况。

举个例子，以下记录中第3条是无效的：

1 | task 1 | 08-04-2023 12:00:00 | 08-04-2023 15:00:00
2 | task 2 | 08-04-2023 13:00:00 | 08-04-2023 16:00:00
3 | task 3 | 08-04-2023 14:00:00 | 08-04-2023 15:50:00 <== 应晚于16:00
4 | task 4 | 08-04-2023 15:00:00 | 08-04-2023 17:00:00

task3的创建时间晚于task2，但完成时间更早，违反了顺序要求。

SQL解决方案 #

虽然可以用PL/SQL逐行处理实现校验，但更高效的方式是直接用SQL查询找出不符合规则的订单：

select * from ORDERS 
where id in ( select o.id from ORDERS o, ORDERS o2
where ( o.creation > o2.creation and o2.completed > o.completed));

逻辑说明 #

子查询会筛选出所有满足以下条件的订单ID：

• 当前订单o的创建时间晚于另一订单o2
• 但当前订单o的完成时间早于订单o2

外层查询则返回这些不符合顺序要求的订单详情。

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