如何通过SQL生成用户ID
问题描述:
我想生成userID,逻辑是,UserID是FirstName和LastName的组合。如果userID已经存在,则在FirstName和LastName后面加1。如何通过SQL生成用户ID
例如:
用户John Doe的用户ID将是JohnDoe。
如果另一个John Doe加入公司,那么UserID应该是JohnDoe1等等。
我正在使用的案例陈述是在下面。
Select distinct case
when lower(a.First_Name) || lower(a.Last_Name) = b.Name then a.First_Name || a.Last_Name || 1
when lower(a.First_Name) || lower(a.Last_Name) || 1 = b.Name then a.First_Name || a.Last_Name || 2
else a.first_name || a.last_name
end as user_id from Users a, accounts b
where a.unique_id = '12345' (This is matching key from table B)
在我的表中,我已经有了用户ID JohnDoe和JohnDoe1。
和输出我得到上述查询的是,
JOHNDOE JohnDoe1 JohnDoe2
如何我只得到JohnDoe2作为输出。
答
下面是可能在甲骨文工作的解决方案:
WITH oldnames AS (
SELECT 'JohnDoe' AS user_id, 'John' AS fname, 'Doe' AS lname
FROM dual
UNION
SELECT 'JohnDoe1' AS user_id, 'John' AS fname, 'Doe' AS lname FROM dual
UNION
SELECT 'MaxHeadroom' AS user_id, 'Max' AS fname, 'Headroom' AS lname FROM dual
), newnames AS (
SELECT 'John' AS fname, 'Doe' AS lname FROM dual
UNION
SELECT 'Max' AS fname, 'Headroom' AS lname FROM dual
UNION
SELECT 'Mark' AS fname, 'Twain' AS lname FROM dual
)
SELECT n1.fname, n1.lname, MAX(n2.user_id)
, n1.fname || n1.lname ||
CASE WHEN MAX(n2.user_id) IS NOT NULL THEN TO_CHAR(COALESCE(TO_NUMBER(MAX(REGEXP_SUBSTR(n2.user_id, '\d+$'))), 0) + 1) END
FROM newnames n1, oldnames n2
WHERE n1.fname = n2.fname(+)
AND n1.lname = n2.lname(+)
GROUP BY n1.fname, n1.lname
在这里,我假设你有在你存储的用户ID与姓和名一起的表。这就是我在上面的查询中所称的oldnames
,其中newnames
是您要为其生成ID的用户表。
您使用的是SQL Server还是Oracle? –