自动生成唯一键不一致

Question

我正在尝试创建一个唯一的密钥，格式如下：

ABC123456

例如，它从ABC开始，到ABC999999结束，基本上前三个字符是ABC，然后是6个数字。

我目前有以下问题：

  SELECT 
    'ABC' + CAST(REPLICATE('0',6-LEN(RTRIM(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))))+ (SELECT Counter from Counters)) + RTRIM(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + (SELECT Counter from Counters)) as CHAR(9)) as InvoiceNumber,
    ID,
    RandomNumber
 from
    RandomNumbers

以下是一些示例数据：

CREATE TABLE [dbo].[Counters](
    [Counter] [int] NULL
) ON [PRIMARY]
INSERT INTO [dbo].[Counters]
           ([Counter])
     VALUES
           (0)

CREATE TABLE [dbo].[RandomNumbers](
    [ID] [int] NULL,
    [RandomNumber] [int] NULL
) ON [PRIMARY]
GO

with randowvalues
    as(
       select 1 id, CAST(RAND(CHECKSUM(NEWID()))*100 as int) randomnumber
        union  all
        select id + 1, CAST(RAND(CHECKSUM(NEWID()))*100 as int)  randomnumber
        from randowvalues
        where 
          id < 1000
      )

Insert into RandomNumbers
(
ID,
RandomNumber
)
select *
from randowvalues
OPTION(MAXRECURSION 0)

您将看到它最初运行良好，但如果您运行下面的示例：

Update Counters
Set Counter = 1

然后重新运行主查询，它会抛出编号。我在这里做错了什么？

Answer 1

使用STUFF()函数：

with
    cte
as
(
    SELECT 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + (SELECT Counter from Counters) as InvNo,
        ID,
        RandomNumber,
        'ABC000000' as SampleNo
    from
        RandomNumbers
)
select
    stuff(SampleNo, (Len(SampleNo) - Len(InvNo)) + 1, Len(SampleNo), InvNo) as InvoiceNumber,
    ID,
    RandomNumber
from
    cte;

Answer 2

这个怎么样，如果你有一个IDENTITY列的话会更好

SELECT STUFF('ABC000000',
             10 - LEN( CAST(RN AS VARCHAR) ),
             LEN(CAST(RN AS VARCHAR)),
             CAST(RN AS VARCHAR)
       ) GenKey,
       SomeCols
FROM
(
   SELECT *,
          ROW_NUMBER() OVER(ORDER BY SomeCols) RN --Simulate IDENTITY column if you have one it would be better
   FROM YourTable 
) T;

返回：

+-----------+----------+
|  GenKey   | SomeCols |
+-----------+----------+
| ABC000001 | SomeData |
| ABC000002 | SomeData |
| ABC000003 | SomeData |
| ABC000004 | SomeData |
| ABC000005 | SomeData |
| ABC000006 | SomeData |
| ABC000007 | SomeData |
| ABC000008 | SomeData |
| ABC000009 | SomeData |
| ABC000010 | SomeData |
| …         |          |
+-----------+----------+

如果你已经有了一个IDENTITY列，那么即使从YourTable中删除了一些行，甚至YourTable有比999999更多的行，也能得到正确的GenKey，这是一个模拟这种情况的。