α数字ID格式

Question

我使用字段LastName的前三个字符生成一个alpha数字ID，然后递增3位数。我在MSSQL 2012中使用的查询是：

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

但是，查询工作相对良好，但是，一旦数值增加到两个，而不是结束于010，则为00010。当数字部分增加到三位数时，也是如此。例如，我希望输出如下所示：

╔══════════╦═══════════╦══════════╗
║ 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      ║ SMI010   ║
║ Smith    ║ John      ║ SMI011   ║
║ Smith    ║ Robert    ║ SMI012   ║
║  ..      ║    ..     ║   ..     ║
║ Smith    ║ James     ║ SMI100   ║
╚══════════╩═══════════╩══════════╝

我不知道如何处理递增的数值字段。谢谢你能提供的任何帮助。

Answer 1

从右边而不是左边？

SELECT *, UPPER(LEFT(LastName, 3)) + RIGHT('000' + CAST(ID AS VARCHAR(3)), 3)
FROM (
    SELECT 
        LastName, 
        FirstName, 
        ROW_NUMBER() OVER (PARTITION BY LEFT(LastName, 3) ORDER BY LastName) AS ID
    FROM @table
) T