如何增加自定义唯一ID？ [英] How to increment Custom Unique ID?

问题描述

我有一张表，我们需要在其中创建一个唯一的ID 15位数。但它应该自动递增。假设我的唯一ID类似于

M02192015001

它应该根据日期和最后三位数递增，例如

M02202015002

如果在同一日期还有一个条目应该是

M02212015003



现在我想知道发展这种要求的想法。我是否应该从DB中获取现有数字，如果它在同一天，则只增加1？或者还有其他什么吗？



有谁能给我一些这些自定义自动增量ID的想法？

I have a table in which we need to create a unique ID 15 digit. But It should be auto incremented. Suppose My unique Id is something like
"M02192015001"
It should get incremented based on date and also last three digits like
"M02202015002"
If there is one more entry on same date it should be like
"M02212015003"

Now I want to know ideas to develop such requirement. Should I get the existing number from DB and simply increment by 1 if it is on same date? Or is there anything other that?

Can anyone give me some ideas for these kind of custom auto increment ID?

推荐答案

在我看来，问题是你把三个单独的东西放在一个列中。将数据分成三列：

- 前缀（包含M）数据类型varchar？

- 包含日期dtatype日期的itemdate

- 计数器数据类型int，在一个日期内总是递增1

然后在这三列上定义一个唯一约束，以确保永远不会有重复。



当显示自定义ID时，只需连接并格式化这三列的值。



但是，如果你真的不需要这样的自定义ID，我建议使用 uniqueidentifier [ ^ ]。它保证是唯一的，你不必担心产生价值。

In my opinion the proble is that you have put the three separate things into a single column. Break the data into three columns:
- prefix (containing M) datatype varchar?
- itemdate containing the date dtatype date
- counter datatype int, always incremented by 1 within a date
then define a unique constraint on these three columns to ensure to never have a duplicate.

When showing the custom id just concatenate and format the values of these three columns.

However, if you don't really need a custom ID like this I would suggest using uniqueidentifier[^]. It's guaranteed to be unique and you don't have to worry so much about generating the value.

我打算将此作为评论发布到解决方案1但是更容易获得格式用解决方案而不是评论...



你说你已经有了一个独特的身份证，我想更多关于约会中的独特计数器 。这篇CP文章给了我这个想法 - 如何使用ROW_NUMBER（）枚举和分区SQL Server中的记录 [ ^ ]



使用这个原则我想创建一个独特的 - 在一个日期标识符...



我创建了一个样本表，其中包含

I was going to post this as a comment to solution 1 but it's easier to get the formatting with a "solution" than a comment ...

You said you already had a unique ID and I thought a bit more about the "unique counter within a date". This CP article gave me the idea - How to use ROW_NUMBER() to enumerate and partition records in SQL Server[^]

Using that principle I wanted to create a unique-within-a-date identifier...

I created a sample table with this

CREATE TABLE [dbo].[CP1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [recDate] [date] NOT NULL,
    [someData] [nchar](10) NULL,
    [NewId] [int] NULL
) ON [PRIMARY]

我不会重复我插入的任何样本数据（除非你问我）但我想要的东西是不必生成另一个唯一的ID。



这个sql查询将根据日期和标识列格式化您的唯一ID

I won't repeat any sample data I inserted (unless you ask me to) but the thing I was trying to get to was not having to generate another unique ID.

This sql query will "format" your unique ID based on the date and identity column

WITH T1(ID, recDate, someData, NewId)
AS
(
    SELECT [ID],recDate,someData,
     ROW_NUMBER() over(PARTITION BY recDate
                         ORDER BY ID ASC) Number
    FROM CP1
)
select ID, recDate, someData, 'M' +
        REPLACE(CONVERT(CHAR(10), recDate, 103), '/', '')
        + Replace(Str(NewId, 7), ' ' , '0')
        from T1 where ID=@input

其中 ID = @ input 就是这样你想要识别你所追踪的记录。



您可以将其放入表格的插入触发器中以生成ID并将其保留在桌面上，或者在提取显示时使用它。



一个重要的警告 - 如果ID仅在显示时生成，如果记录被删除，它将会改变。如果持续存在（通过触发器）则无论如何都应该有效。



请注意这是对解决方案1的补充 - 不是替代

where ID=@input is however you want to identify the record you are after.

You could put this into a insert trigger on the table to generate the ID and persist it on the table, or use it when extracting for display.

One important caveat - if the ID is generated at display time only, it would change if records had been deleted. If persisted (via a trigger) then it should work regardless.

Please note this is meant to be complementary to Solution 1 - not an alternative

这篇关于如何增加自定义唯一ID？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！