生成字母数字客户ID

Question

我正在从遗留系统中迁移数据。为了适应我们的新系统，需要生成适当的客户ID。我正在寻找一种方法，以产生唯一的字母数字ID的部分基于客户的姓氏。要使用的约定是客户姓氏的前3个字母，后面是一个3位数字标识符。例如：

Smith, John = SMI001
Smith, Robert = SMI002
Jones, David = JON001

新系统将自动生成新的客户id，我只需要一个脚本来运行一次数据库，我甚至不知道从哪里开始。谢谢你能提供的任何帮助！

Answer 1

测试数据

DECLARE @Table TABLE(LastName NVARCHAR(50), FirstName NVARCHAR(50), ID NVARCHAR(50))
INSERT INTO @Table(LastName, FirstName) VALUES 
('Smith', 'John'),('Smith', 'Robert'),('Jones', 'David'),('Smith', 'John'),
('Smith', 'John'),('Smith', 'Robert'),('Jones', 'David'),('Smith', 'John'),
('Smith', 'John'),('Smith', 'Robert'),('Jones', 'David'),('Smith', 'John'),
('Smith', 'John'),('Smith', 'Robert'),('Jones', 'David'),('Smith', 'John')

查询

SELECT LastName, FirstName,
       UPPER(LEFT(LastName, 3)) + LEFT('000', LEN(ROW_NUMBER()
                   OVER (PARTITION BY  LEFT(LastName, 3)ORDER BY LEFT(LastName, 3)) )+1)
      +  CAST(ROW_NUMBER() OVER (PARTITION BY  
               LEFT(LastName, 3)ORDER BY LEFT(LastName, 3))  AS NVARCHAR(1000)) AS ID
FROM @Table

结果集

╔══════════╦═══════════╦══════════╗
║ LastName ║ FirstName ║    ID    ║
╠══════════╬═══════════╬══════════╣
║ Jones    ║ David     ║ JON001   ║
║ Jones    ║ David     ║ JON002   ║
║ Jones    ║ David     ║ JON003   ║
║ Jones    ║ David     ║ JON004   ║
║ Smith    ║ John      ║ SMI001   ║
║ Smith    ║ John      ║ SMI002   ║
║ Smith    ║ Robert    ║ SMI003   ║
║ Smith    ║ John      ║ SMI004   ║
║ Smith    ║ John      ║ SMI005   ║
║ Smith    ║ Robert    ║ SMI006   ║
║ Smith    ║ John      ║ SMI007   ║
║ Smith    ║ John      ║ SMI008   ║
║ Smith    ║ Robert    ║ SMI009   ║
║ Smith    ║ John      ║ SMI00010 ║
║ Smith    ║ John      ║ SMI00011 ║
║ Smith    ║ Robert    ║ SMI00012 ║
╚══════════╩═══════════╩══════════╝

Answer 2

下面是一个查询，演示如何生成这样的ID：

SELECT  FirstName,
        LastName,
        Code = UPPER(LEFT(LastName, 3)) 
                + RIGHT('000' 
                    + CAST(
                        Row_Number() OVER ( PARTITION BY LastName 
                                            ORDER BY FirstName) 
                        AS NVARCHAR(3))
                    , 3)
FROM    [Table1]