SQL Server-创建自定义自动增量字段 [英] SQL Server - Create a custom auto-increment field

问题描述

我正在尝试在sql中产生自定义自动递增功能.我的自定义自动插入ID应该如下所示...

I am trying to produce a custom auto-increment functionality in sql. my custom auto-incerement ID should be like below...

S1501.001

"S"代表供应商名称的首字母.

"S" is for Supplier's name first letter.

"15"代表今年的后两位数字.

"15" is for this year's last 2 digits.

"01"是今天的月份

".永远在那里

"001"是我的增量器.

"001" is my incrementer.

计数器将像下面一样继续

the counter will go on like below

S1501.001
S1501.002
S1501.003
S1501.004

首先，我必须找到"S1501".并找到末尾数字最高的ID.我可以创建一个新的"S1501.005".我该怎么办?

Firstly, I have to find the "S1501." and find the ID with highest digits at the end. I can create a new "S1501.005". How can I do this?

我做了一些事情，但是没有工作.

I have done something but didnt work.

SELECT TOP 1 (SELECT SUBSTRING('S1501.001', 7,3)),* 
FROM LG_001_01_SERILOTN 
WHERE  CODE LIKE SUBSTRING('S1501.001', 1,6)+'%'
ORDER BY (SELECT SUBSTRING('S1501.001', 7,3)) DESC

推荐答案

最好的解决方案是使用

• ID INT IDENTITY(1,1)列可让SQL Server处理数字值的自动递增
• 一个计算后的持久化列，可将该数字值转换为所需的值

• an ID INT IDENTITY(1,1) column to get SQL Server to handle the automatic increment of your numeric value
• a computed, persisted column to convert that numeric value to the value you need

所以尝试一下:

CREATE TABLE dbo.tblCompany
  (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
   CompanyID AS 'S1501.' + RIGHT('000' + CAST(ID AS VARCHAR(3)), 3) PERSISTED,
   .... your other columns here....
  )

现在，每次在tblCompany中插入一行而未指定ID或CompanyID的值时:

Now, every time you insert a row into tblCompany without specifying values for ID or CompanyID:

INSERT INTO dbo.tblCompany(Col1, Col2, ..., ColN)
VALUES (Val1, Val2, ....., ValN)

然后，SQL Server将自动安全地增加您的ID值，并且CompanyID将包含诸如S1501.001，S1501.002，......之类的值-自动，安全，可靠，无重复.

then SQL Server will automatically and safely increase your ID value, and CompanyID will contain values like S1501.001, S1501.002,...... and so on - automatically, safely, reliably, no duplicates.

这篇关于SQL Server-创建自定义自动增量字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！