如何编写SQL查询识别表中所有回溯插入的ID？

表结构与规则 #

• 表包含Id和Timestamp两个字段：
  • Id是严格递增序列，新增记录时ID(n) = ID(n-1) + 1
  • Timestamp为时间戳，回溯插入的记录其时间戳小于此前所有记录的最大时间戳

回溯插入的定义 #

满足以下条件的记录即为回溯插入：

ID(n) > ID(n-1)
Timestamp(n) < max(timestamp(1):timestamp(n-1))

示例表 #

其中ID为5和6的记录属于回溯插入（它们的时间戳小于之前所有记录的最大时间戳）。

解决方案 #

方法1：窗口函数（高效推荐） #

利用窗口函数MAX() OVER()，按Id顺序计算当前记录之前所有行的最大时间戳，再筛选出时间戳小于该最大值的记录：

SELECT Id
FROM (
    SELECT 
        Id,
        Timestamp,
        -- 计算当前行之前所有记录的最大时间戳
        MAX(Timestamp) OVER (ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_max_ts
    FROM your_table_name
) AS sub_query
WHERE Timestamp < prev_max_ts;

这里的ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING明确指定只取当前行之前的所有记录来计算最大值，完全符合需求。

方法2：关联子查询（兼容旧版本SQL） #

如果你的SQL环境不支持窗口函数，可以用关联子查询实现：

SELECT t1.Id
FROM your_table_name t1
WHERE t1.Timestamp < (
    -- 找出所有ID比当前记录小的最大时间戳
    SELECT MAX(t2.Timestamp)
    FROM your_table_name t2
    WHERE t2.Id < t1.Id
);

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