如何创建由其他列细分的自动递增列 [英] How to create an auto increment column that is segmented by an other column

问题描述

我需要创建一个包含增量ID的表，但是我希望这些ID根据另一列自动进行细分.这就是我想要的:

I need to create a table that will contain a incremental id, but I would like the ids be automatically segmented according to an other column. Here is what I want :

CREATE TABLE dbo.MyTable (
    myKey INT IDENTITY PRIMARY KEY,
    category INT,
    incrementalId INT
);

INSERT INTO dbo.MyTable (category) VALUES (100);
INSERT INTO dbo.MyTable (category) VALUES (200);
INSERT INTO dbo.MyTable (category) VALUES (100);
INSERT INTO dbo.MyTable (category) VALUES (100);
INSERT INTO dbo.MyTable (category) VALUES (100);
INSERT INTO dbo.MyTable (category) VALUES (200);

SELECT *
FROM dbo.MyTable;

我希望它显示类似以下内容:

I would like this to display something like :

myKey       category    incrementalId
----------- ----------- -------------
1           100         1
2           200         1
3           100         2
4           100         3
5           100         4
6           200         2

意思是我希望incrementalId在每个类别中自动递增，并为插入的任何新类别从1重新开始.我希望在表中的任何插入处自行完成此操作(我不想记住在插入此表时进行的插入操作).

Meaning I want the incrementalId to be automatically incremented per category and restart from 1 for any new category inserted. I want this to be done by itself on any inserts in the table (I don't want to have to remember to do that when I insert in this table).

我认为这可能是通过窗口函数或触发器来完成的，但我只是想不通.

I think this might be done with window functions and maybe a trigger, but I just can't figure how.

我希望数据能够持久保存，以避免在发生数据删除的情况下增量ID发生移位.另外，理想情况下，在删除行时，不会重新赋予相同的ID(序列或IDENTITY的工作方式相同)

I would like the data to be persisted to avoid incrementalId to be shifted if data deletion happens. Also, ideally the same ID would not be re-given in the event of rows deletion (the same way that sequences or IDENTITY works)

有什么主意吗?

推荐答案

CREATE TABLE dbo.MyTable (
  myKey INT IDENTITY PRIMARY KEY,
  category INT,
  incrementalId INT
);
GO

create table dbo.nextCategoryID (
  category int,
  nextidvalue int,
  constraint PK_nextCategoryID primary key clustered( category, nextidvalue )
);
GO

create trigger numberByCategory on dbo.MyTable
after insert as 

-- Automatically add any net new category
insert into dbo.nextCategoryID ( category, nextidvalue )
    select distinct category, 1 as nextidvalue
    from inserted
    where not exists ( select * from dbo.nextCategoryID s
        where s.category = inserted.category );


-- Number the new rows in each incoming category
with numberedrows as (
    select 
        i.myKey, 
        i.category, 
        n.nextidvalue - 1 + row_number() over ( partition by i.category order by i.category ) as incrementalId
    from inserted i
    join dbo.nextCategoryID n on i.category = n.category
)
update m
    set incrementalId = n.incrementalId
from dbo.MyTable m
join inserted i on m.myKey = i.myKey
join numberedrows n on n.myKey = i.myKey;


update dbo.nextCategoryID
    set nextidvalue = 1 + ( select max( m.incrementalId ) 
        from inserted i 
        join dbo.MyTable m on i.myKey = m.myKey
        where i.category = nextCategoryID.category 
    )
where exists ( select * 
    from inserted i
    where i.category = nextCategoryID.category
);

GO

-- Test data 

INSERT INTO dbo.MyTable (category) VALUES (100);
INSERT INTO dbo.MyTable (category) VALUES (200);
INSERT INTO dbo.MyTable (category) VALUES (100);
INSERT INTO dbo.MyTable (category) VALUES (100);
INSERT INTO dbo.MyTable (category) VALUES (100);
INSERT INTO dbo.MyTable (category) VALUES (200);

insert into dbo.MyTable (category) 
values 
    ( 200 ),
    ( 200 ),
    ( 100 ),
    ( 300 ),
    ( 400 ),
    ( 400 )


SELECT *
FROM dbo.MyTable;

这篇关于如何创建由其他列细分的自动递增列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！