如何查询数据库中首个打破顺序规则的recordNumber？

正常情况下，自增字段recordNumber递增时，insertedOn应该同步递增（每次插入取当前时间），即按recordNumber排序的insertedOn序列和按insertedOn排序的序列完全一致。要找到首个打破这个规则的recordNumber，可以用以下两种方法：

方法一：使用窗口函数（推荐，支持现代SQL数据库如PostgreSQL、MySQL 8+、SQL Server等） #

通过LAG窗口函数获取按recordNumber排序后前一条记录的insertedOn，然后筛选出当前记录时间早于前一条的第一条记录：

SELECT recordNumber
FROM (
    SELECT 
        recordNumber,
        insertedOn,
        LAG(insertedOn) OVER (ORDER BY recordNumber) AS prev_insertedOn
    FROM your_table_name
) AS sub_query
WHERE insertedOn < prev_insertedOn
ORDER BY recordNumber
LIMIT 1;

逻辑说明 #

1. 子查询中，LAG(insertedOn) OVER (ORDER BY recordNumber)会为每条记录返回按recordNumber排序的上一条记录的insertedOn值
2. 外层查询筛选出当前记录insertedOn小于上一条的记录，按recordNumber升序排列后取第一条，就是首个打破顺序的记录

方法二：自连接查询（兼容不支持窗口函数的老版本数据库） #

通过自连接关联连续recordNumber的两条记录，比较它们的insertedOn：

SELECT t1.recordNumber
FROM your_table_name t1
INNER JOIN your_table_name t2 
    ON t1.recordNumber = t2.recordNumber + 1
WHERE t1.insertedOn < t2.insertedOn
ORDER BY t1.recordNumber
LIMIT 1;

逻辑说明 #

1. 自连接条件t1.recordNumber = t2.recordNumber + 1确保t1是t2的下一条自增记录
2. 筛选出t1的insertedOn早于t2的情况，按t1.recordNumber升序取第一条，即为首个异常记录

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