Oracle从UserID字符串中选择最大整数值

Question

我希望在我的Oracle数据库表中选择与UserID关联的整数的UserID值，以便为具有类似UserID的用户生成下一个用户名。

UserID包含如下所示的值。整数之前没有固定的字符模式，因为字符串是用户名。

TKe10
TKe9
TKe12
TomKelly13
TomKelly9
PJames12
PJames7

我尝试使用下面的查询，但它总是将TKe9或TomKelly9或PJames7作为MAX值。

SELECT * FROM 
              (SELECT MAX(UserID) from PV_USERS
              WHERE REGEXP_LIKE (UserID, '^'|| '<some_user_id>'|| '[^A- 
              Za-z][0-9]*'));

我也尝试过使用ORDER BY DESC WHERE ROWNUM<=1，但它也提供了相同的输出。

Answer 1

只需提取ID的数字部分，就可以使用

regexp_substr(userid, '[0-9]*$')

然后，在找到最大值之前，将其转换为一个数字(否则，您仍然要进行字符串比较，并在10之前对9进行排序)：

max(to_number(regexp_substr(userid, '[0-9]*$')))

而且您可能希望允许您正在检查的ID根根本不存在，这可以通过nvl()或coalesce()实现。

select coalesce(max(to_number(regexp_substr(userid, '[0-9]*$'))), 0) as max_num
from pv_users
where regexp_like(userid, '^'|| 'TomKelly'|| '[0-9]*');

   MAX_NUM
----------
        13

select coalesce(max(to_number(regexp_substr(userid, '[0-9]*$'))), 0) as max_num
from pv_users
where regexp_like(userid, '^'|| 'PJames'|| '[0-9]*');

   MAX_NUM
----------
        12

select coalesce(max(to_number(regexp_substr(userid, '[0-9]*$'))), 0) as max_num
from pv_users
where regexp_like(userid, '^'|| 'NewName'|| '[0-9]*');

   MAX_NUM
----------
         0

..。然后添加1并将其追加回根以获得下一个ID。

根据您的业务规则，您可能希望使筛选器不区分大小写。

您应该知道，同时执行此操作的两个会话将看到相同的结果，因此两者都将尝试创建相同的ID，例如TomKelly14。您要么需要序列化此生成操作，要么在尝试将新值插入表并重复时，需要进行类似于回退的检查，以检查是否出现PK冲突。

Answer 2

with temp as
(
select 'Tke10' userid from dual
union all
 select 'Tke9' userid from dual
union all
 select 'Tke12' userid from dual
union all
 select 'Tomkelly13' userid from dual
union all
 select 'Tomkelly9' userid from dual
union all
 select 'Pjames12' userid from dual
union all
 select 'Pjames7' userid from dual
 )

    select A||B from (
 select 
 substr(userid,1,instr(userid,to_number(regexp_substr(userid,'\d+$')))-1) A
,max(to_number(regexp_substr(userid,'\d+$'))) B
from temp
 group by substr(userid,1,instr(userid,to_number(regexp_substr(userid,'\d+$')))-1)
)
 ;