SQL创建表，该表基于另一列自动递增一列 [英] SQL Create Table that Auto-Increments one column based on another column

问题描述

我仍然是SQL的初学者，所以请多多包涵.我需要创建一个表，用于存储来自表单的信息.每个表单都有一个与之关联的ID，该ID由两部分组成.第一部分是MMYY，第二部分是5位自动递增的整数. ID的示例是0714-00001.并且每当月份改变时，5位数编号应重新开始.因此，我希望这些列看起来像这样:

I'm still a beginner with SQL so please bear with me. I need to create a table that stores information from a form. Each form has an ID associated with it that has two parts. The first part is MMYY and the second part is a 5 digit auto-incrementing integer. An example ID is 0714-00001. And every time the month changes, the 5 digit numbering should start over. So, I want the columns to look something like this:

订单号. | Order_ID
----------------------------
0714        |  0001
0714        |  0002
0714        |  0003
0814        |  0001
0814        |  0002
0914        |  0001
当SQL Server中的月份发生变化时，是否有任何方法可以使自动递增列重新开始递增?

Order_No. | Order_ID
----------------------------
0714         | 0001
0714         | 0002
0714         | 0003
0814         | 0001
0814         | 0002
0914         | 0001

Is there any way to get the auto-incrementing column to restart it's incrementing when the month changes in the SQL-Server?

我也环顾四周，建议使用PHP进行此操作的答案很少，但是由于我使用的是CMS，因此无法输入自己的任何PHP脚本.

I've also looked around and few answers suggested using PHP to do this, but because I am using a CMS, I can't enter any PHP script of my own.

更新: 我最终只是在CMS站点中编写了一个IF语句，以查询以表格形式输入的order_no是否与现有表中的order_no相匹配.如果它们匹配，则将新行插入到现有表中，如果不匹配，则将现有表中的所有数据复制到备份表中，然后将现有表截断，然后将新表删除.已插入.因此，允许表再次从1重新开始计数.

UPDATE: I ended up just writing an IF statement in the CMS site for whether the order_no entered in the form matched the order_no in the existing table or not. If they matched the new row would be inserted into the existing table and if they didn't match, all the data from the existing table would be copied to a backup table, the existing table would then be truncated, and then the new would be inserted. Thus allowing the table to restart the counting at 1 again.

推荐答案

可能您可以编写after insert trigger，在其中可以验证月份是否已更改，然后相应地重新设定种子身份.我试图描述您的情况，下面是相同的示例代码

Probably you can write a after insert trigger where you can validate whether the month changed and then reseed identity accordingly. I have tried to depict the scenario of yours and below is the sample code for the same

创建并插入语句

CREATE TABLE Table1
    ([Order_No] varchar(4), [Order_ID] int not null identity)
;

INSERT INTO Table1
    ([Order_No])
VALUES
    ('0714'),('0714'),('0714');

select * from table1;

创建触发器

create trigger trg_reseed on test.dbo.table1
after insert
as 
IF EXISTS (SELECT 1 FROM Table1 t1 
             JOIN inserted i ON t1.[Order_No] = i.[Order_No])
begin
DBCC CHECKIDENT (Table1, reseed, 1)
end

测试场景:

身份种子值没有变化，由于月份相同，它将增加到下一个值

No change in Identity seed value, it will increment to next value since month is same

INSERT INTO Table1
    ([Order_No])
VALUES
    ('0714');

因为插入的月份不同，所以对身份值进行了加密

Reseed the identity value since inserted month is different

INSERT INTO Table1
    ([Order_No])
VALUES
    ('0914');

希望这会让您有所了解并开始使用.

Hope this will get you idea and get started.

这篇关于SQL创建表，该表基于另一列自动递增一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！