增加 SQL 中的自定义主键值 [英] Incrementing custom primary key values in SQL

问题描述

我被要求为主键列生成自定义 ID 值.查询如下，

I am asked to generate custom ID values for primary key columns. The query is as follows,

SELECT * FROM SC_TD_GoodsInward WHERE EntityId = @EntityId
        SELECT @GoodsInwardId=IIF((SELECT COUNT(*) FROM SC_TD_GoodsInward)>0, (Select 'GI_'+RIGHT('00'+CONVERT(varchar,datepart(YY,getdate())),2)+RIGHT('00'+CONVERT(varchar,datepart(MM,getdate())),2)+RIGHT('00'+CONVERT(varchar,datepart(DD,getdate())),2)+'_'+CONVERT(varchar,@EntityId)+'_'+(SELECT RIGHT('0000'+CONVERT(VARCHAR,CONVERT(INT,RIGHT(MAX(GoodsInwardId),4))+1),4) from SC_TD_GoodsInward)), (SELECT 'GI_'+RIGHT('00'+CONVERT(varchar,datepart(YY,getdate())),2)+RIGHT('00'+CONVERT(varchar,datepart(MM,getdate())),2)+RIGHT('00'+CONVERT(varchar,datepart(DD,getdate())),2)+'_'+CONVERT(varchar,@EntityId)+'_0001')) 

这里的 SC_TD_GoodsInward 是一个表，GoodsInwardId 是要生成的值.我也得到了想要的输出.例子.

Here the SC_TD_GoodsInward is a table, GoodsInwardId is the value to be generated. I am getting the desired outputs too. Examples.

GI_131118_1_0001
GI_131212_1_0002
GI_131212_1_0003

但是，当最后一位数字达到 9999 时，上述条件失败.我模拟了查询，结果是，

But, the above condition fails when the last digits reach 9999. I simulated the query and the results were,

GI_131226_1_9997
GI_140102_1_9998
GI_140102_1_9999
GI_140102_1_0000
GI_140102_1_0000
GI_140102_1_0000
GI_140102_1_0000
GI_140102_1_0000

9999 之后，变为 0000，此后不再递增.所以，在未来，我最终会遇到PK重复错误.我如何回收这些值，以便在 9999 之后继续作为 0000、0001 等.我在上述查询中遗漏了什么?

After 9999, it goes to 0000 and does not increment thereafter. So, in the future, I will eventually run into a PK duplicate error. How can i recycle the values so that after 9999, it goes on as 0000, 0001 ... etc. What am I missing in the above query?

注意:请在查询中将 @EntityId 值视为 1.我使用的是 SQL SERVER 2012.

NOTE: Please consider the @EntityId value to be 1 in the query. I am using SQL SERVER 2012.

推荐答案

在给出问题的解决方案之前，请先针对您的问题提出几点:

Before giving a solution for the question few points on your question:

1. 由于自定义主键主要由日期(140102)、交易发生的物理位置(entityID)、4 个位置编号(9999) 三个部分组成.
2. 根据设计，单个物理位置的单个日期不能超过 9999 笔交易 -- 我的解决方案也将包含相同的限制.

我的解决方案的一些要点

Some points on my solution

1. 第 4 位数字与日期相关联，这意味着新日期的计数从 0000 开始.例如GI_140102_1_0001，GI_140102_1_0002，GI_140102_1_0003，GI_140103_1_0000，GI_140104_1_0000

无论如何，这个字段都是唯一的.

Any way the this field will be unique.

1. 该解决方案将记录中的最新日期与当前日期进行比较.逻辑:如果记录中的当前日期和最新日期匹配然后它将 4 位数字增加 1如果记录中的当前日期和最新日期不匹配它通过值 0000 设置第 4 位数字.

解决方案:(下面的代码给出了下一个 GoodsInwardId 的值，根据需要使用它以适应您的解决方案)

The Solution: (Below code gives out the value which will be the next GoodsInwardId, Use it as per requirement to fit in to your solution)

declare @previous nvarchar(30);
declare @today nvarchar(30);
declare @newID nvarchar(30);
select @previous=substring(max(GoodsInwardId),4,6) from SC_TD_GoodsInward;
Select @today=RIGHT('00'+CONVERT(varchar,datepart(YY,getdate())),2)
+RIGHT('00'+CONVERT(varchar,datepart(MM,getdate())),2)+RIGHT('00'+CONVERT(varchar,datepart(DD,getdate())),2);

if @previous=@today
BEGIN
Select @newID='GI_'+RIGHT('00'+CONVERT(varchar,datepart(YY,getdate())),2)
+RIGHT('00'+CONVERT(varchar,datepart(MM,getdate())),2)+RIGHT('00'+CONVERT(varchar,datepart(DD,getdate())),2)
+'_'+CONVERT(varchar,1)+'_'+(SELECT RIGHT('0000'+
CONVERT(VARCHAR,CONVERT(INT,RIGHT(MAX(GoodsInwardId),4))+1),4) 
from SC_TD_GoodsInward);
END
else
BEGIN
SET @newID='GI_'+RIGHT('00'+CONVERT(varchar,datepart(YY,getdate())),2)
+RIGHT('00'+CONVERT(varchar,datepart(MM,getdate())),2)+RIGHT('00'+CONVERT(varchar,datepart(DD,getdate())),2)
+'_'+CONVERT(varchar,1)+'_0000';
END
select @newID;

T-SQL 创建所需的结构(Probable Guess)

T-SQL to create the required structure (Probable Guess)

对于表:

CREATE TABLE [dbo].[SC_TD_GoodsInward](
    [EntityId] [int] NULL,
    [GoodsInwardId] [nvarchar](30) NULL
)

表的示例记录:

insert into dbo.SC_TD_GoodsInward values(1,'GI_140102_1_0000');
insert into dbo.SC_TD_GoodsInward values(1,'GI_140101_1_9999');
insert into dbo.SC_TD_GoodsInward values(1,'GI_140101_1_0001');

**它在您的情况下是一个可能的解决方案，尽管完美的解决方案是拥有标识列(如果需要，请使用重新种子)并将其与当前日期联系起来作为计算列.

**Its a probable solution in your situation although the perfect solution would be to have identity column (use reseed if required) and tie it with the current date as a computed column.

这篇关于增加 SQL 中的自定义主键值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！