甲骨文 11g - 找出新的独特值

Question

我有一个用户表，其中一列是User_ID（命名策略=名字的第一个字母，其余是姓氏。如果名称存在，则递增 1）。

例如：约翰·多伊 -> user_id=jdoe

如果 jdoe 已经存在，则 user_id = jdoe1

查询的输入是一个基本user_id（例如 jdoe）我想扫描表并使查询的输出为：一个新的非重复值 user_id。

如果表具有以下值：

jdoe
jdoe1
jdoe2
jdoe3

输出应jdoe4 。

任何帮助将不胜感激。

谢谢

附言

不允许对表进行更改。

查询中的值正与另一个系统（活动目录）一起使用

Answer 1

这使用内联视图来获取与您的基本 ID 值匹配的任何事物的user_id和数字后缀，然后决定它是否可以使用未经修饰的基值（如果 max(user_id) 为 null，则已经没有类似的内容），如果不是，则添加递增的后缀。

该后缀基于现有的最高后缀，但是如果您到目前为止只有基本名称（jdoe没有数字），则max(suffix)为空，因此nvl()在添加一个之前将其转换为零。

select decode(max(user_id), null, :base, :base || (nvl(max(suffix), 0) + 1))
  as new_user_id
from (
  select user_id, regexp_replace(user_id, '^[[:alpha:]]*', null) as suffix
  from users
  where regexp_like(user_id, '^' || :base || '[^[:alpha:]]*$')
);

使用包含以下内容的起始用户表：

jdoe   
jdoe1  
jdoe2  
jdoe3  
jdoes6 
adoe   

。更改绑定变量以提供基本字符串可为您提供：

jdoe -> jdoe4
jdoes -> jdoes7
adoe -> adoe1
adoes -> adoes

Answer 2

编辑：根据Alex Poole的评论更正了这一点。假设基本名称/首字母不包含数字。

WITH tab
     AS (SELECT 'jdoe' name FROM DUAL
         UNION ALL
         SELECT 'jdoe1' FROM DUAL
         UNION ALL
         SELECT 'jdoe2' FROM DUAL
         UNION ALL
         SELECT 'jdoe3' FROM DUAL
         UNION ALL
         SELECT 'jdoes7' FROM DUAL
         UNION ALL
         SELECT 'jjdoe66' FROM DUAL)
SELECT :newName || TO_CHAR (MAX (id) + 1)
  FROM (SELECT NVL (REGEXP_REPLACE (name, '[^0-9]'), 0) id
          FROM tab
         WHERE REGEXP_LIKE (name, '^' || :newName || '[0-9]'));

带[^0-9] REGEXP_REPLACE将删除所有非数字字符。

带[0-9] REGEXP_LIKE仅显示具有以下数字的匹配项。 ^表示在开头。

:newName是一个绑定变量。