在数据库中查找跳过的参考号

简介

>对于每个购买交易，创建一个参考号。
例如：参考号是pt-stur-2025-1。
在其中的结构：

• > pt：是事务类型

>

• stur：是基于业务域

的代码

• 2025：交易的一年

>

• 1：唯一生成的数字将对创建的每个交易进行自动增量。

>理想情况下，将是这样的：pt-stur-2025-1，pt-stur-2025-2，pt-stur-2025-3等…

>

问题

当服务器已经生成唯一号码并且创建错误时，将跳过参考号。

>我的任务是告诉用户什么是缺少的参考号。

>

解决方案

假设这是public_transaction表

>通过凝视并订购了一个参考号，可以找到，但需要时间。

>

这是获取解决方案的步骤：

>

步骤1。找到可能的模式

select regexp_substr(reference_no, '^(.*?)-(.*?)-(.*?)-(.*?)') as 'pattern', count(*) from purchase_transaction pt  group by pattern order by id asc; 

假设参考号始终具有3个破折号’ – ‘，现在可以尝试找到模式的发生
步骤2。获取下一个唯一生成的数字

select reference_no,  regexp_substr(reference_no, '([0-9]+)$') as 'current_ref_no',  lead(regexp_substr(reference_no, '([0-9]+)$')) over (order by id asc) as 'next_ref_no' from purchase_transaction ai  where reference_no like 'pt-stur-2025-%' order by id asc; 

“ regexp_substr（reference_no，’（[[0-9] ）$’））是需要的，因为只需要在参考号的最高后缀中找到该号码，就不想获得这一年。

获得下一个参考号的关键是使用铅…

> lead（regexp_substr（reference_no，’（[0-9] ）$’））over（id asc订购）为’next_ref_no’ 它告诉sql查看下一行的数据。在上面（）内部，需要按id订购以找到顺序行。

因此，这是输出：

步骤3。找到跳过的起始参考号

如果没有跳过的数字，则表示当前数与下一个数字之间的差异为1。但是，如果有跳过，则差异将不止一个。因此，这是找到的公式：

> next_ref_no -current_ref_no > 1

sql：

WITH FIND_THE_NEXT as (     SELECT id, reference_no,      REGEXP_SUBSTR(reference_no, '([0-9]+)$') as 'current_ref_no',      LEAD(REGEXP_SUBSTR(reference_no, '([0-9]+)$')) OVER (order by id asc) as 'next_ref_no'     FROM purchase_transaction ai      WHERE reference_no like 'PT-STUR-2025-%'     order by id asc )select reference_no, next_ref_no,  case when next_ref_no - current_ref_no > 1 THEN          concat('missing ', (next_ref_no - current_ref_no - 1), ' until ', (next_ref_no - 1))       ELSE          'ok'  END as 'is_missing' from find_the_next order by id asc; 

这是输出：

>从上面，已知已知的丢失参考号是什么。