sql - 在 MS SQL Server 2008 中创建序列

Question

我编写了一个程序，可以在其中请求身份证。

有不同类型的身份证(红色、蓝色、绿色)

在发出请求时，程序应该生成标识号。数字(数字范围)取决于请求的卡。

Red Card: 1 - 50000 
Blue Card: 50001 - 100000 
Green Card: 100001 - 150000

如果我向系统添加新的身份证，则序列应自动为新添加的身份证创建新的号码范围。这些数字不应重复出现。一个号码只能使用一次。

我怎样才能做到这一点？谁能帮我解决这个问题吗？

Answer 1

您可以使用而不是插入触发器来实现此目的

create table Cards_Types (Color nvarchar(128) primary key, Start int);
create table Cards (ID int primary key, Color nvarchar(128));

insert into Cards_Types
select 'RED', 0 union all
select 'BLUE', 50000 union all
select 'GREEN', 100000;

create trigger utr_Cards_Insert on Cards
instead of insert as
begin
    insert into Cards (id, Color)
    select
        isnull(C.id, CT.Start) + row_number() over(partition by i.Color order by i.id),
        i.Color
    from inserted as i
        left outer join Cards_Types as CT on CT.Color = i.Color
        outer apply (
            select max(id) as id
            from Cards as C
            where C.Color = i.Color
        ) as C
end

sql fiddle demo

它允许您一次插入多行:

insert into Cards (Color)
select 'GREEN' union all
select 'GREEN' union all
select 'RED' union all
select 'BLUE'

请注意，您最好在卡片列颜色、ID上建立索引。

另请注意，您只能为每种类型插入 50000 条记录。您可以使用不同的种子，例如 1 个用于“红色”，2 个用于“蓝色”等等，并为例如 100 个类型卡片预留位置:

create table Cards_Types (Color nvarchar(128) primary key, Start int);
create table Cards (ID int primary key, Color nvarchar(128));

insert into Cards_Types
select 'RED', 1 union all
select 'BLUE', 2 union all
select 'GREEN', 3;

create trigger utr_Cards_Insert on Cards
instead of insert as
begin
    insert into Cards (id, Color)
    select
        isnull(C.id, CT.Start - 100) + row_number() over(partition by i.Color order by i.id) * 100,
        i.Color
    from inserted as i
        left outer join Cards_Types as CT on CT.Color = i.Color
        outer apply (
            select max(id) as id
            from Cards as C
            where C.Color = i.Color
        ) as C
end;

sql fiddle demo

这样，“RED”的 ID 始终以 1 结尾，“BLUE”的 ID 始终以 2 结尾，依此类推。