在数据库中查找跳过的参考号-mysql教程-PHP中文网

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

花韻仙語

发布： 2025-02-16 08:14:14

转载

470人浏览过

简介

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

> pt：是事务类型
stur：是基于业务域
2025：交易的一年
1：唯一生成的数字将对创建的每个交易进行自动增量。

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

问题

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

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

解决方案

假设这是public_transaction表

image description

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

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

步骤1。找到可能的模式

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

登录后复制

image description 假设参考号始终具有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。找到跳过的起始参考号 image description

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

> next_ref_no -current_ref_no > 1

sql：

SpeakingPass-打造你的专属雅思口语语料

使用chatGPT帮你快速备考雅思口语，提升分数

查看详情

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;

登录后复制

这是输出：

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