Oracle中查询最后一次UNLOCKED操作后的所有行的问题

我来帮你搞定这个查询问题！你遇到的子查询不允许使用ORDER BY的问题，其实是因为在标量子查询（只返回单个值的查询）里，MAX()聚合函数已经会帮你找到最大的id了，额外加ORDER BY完全是多余的，数据库自然就不允许啦。

最简单的解决方案（假设id自增且与CREATE_DATE顺序一致） #

如果你的id是按时间递增生成的（新记录的id一定比旧记录大），那直接去掉子查询里的ORDER BY就能得到正确结果：

SELECT * 
FROM TABLE 
WHERE id >= (
    SELECT MAX(id) 
    FROM TABLE 
    WHERE ACTION='UNLOCKED' AND action_id=123
)
AND action_id=123; -- 加上这个条件更高效，避免扫描全表

针对你的示例数据，这个查询会找到最后一次UNLOCKED的id=5，然后返回id>=5且action_id=123的行，也就是你期望的id=6、7的记录。

处理id与CREATE_DATE不同步的情况 #

如果存在id大但CREATE_DATE更早的特殊场景，我们需要先锁定最后一次UNLOCKED的日期，再找到该日期下最大的id，确保筛选的是真正的最后一次解锁后的行：

-- 用CTE获取最后一次UNLOCKED的日期和对应最大id
WITH LastUnlocked AS (
    SELECT 
        MAX(CREATE_DATE) AS last_unlock_date,
        MAX(id) AS last_unlock_id
    FROM TABLE 
    WHERE ACTION='UNLOCKED' AND action_id=123
)
SELECT t.*
FROM TABLE t
JOIN LastUnlocked lu 
    ON t.CREATE_DATE >= lu.last_unlock_date 
    AND t.id >= lu.last_unlock_id
WHERE t.action_id=123;

这个方法用公共表表达式先锁定关键信息，再关联主表筛选数据，逻辑清晰，能适配时间和id不一致的复杂场景。

为什么你的原查询报错？ #

你原查询里的ORDER BY CREATE_DATE DESC是多余的——MAX(id)本身就会返回action_id=123且ACTION='UNLOCKED'的记录中最大的id，不需要额外排序。标量子查询只允许返回单个值，加ORDER BY没有实际意义，所以数据库会抛出错误。

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