c# - 基于另一个表的唯一 id 的表的自动递增字母数字 id

Question

我想根据学生所在的系为学生生成一个唯一的 ID。我希望部门 ID 为 CS 时学生 ID 为 CS0448，部门 ID IT 为 IT0448 等等。这些 ID 必须递增 1。

我想我会有另一个身份并为每个添加的学生自动递增 1 并将其与部门 ID 连接，但这对我没有帮助。它会导致 CS0448 旁边添加的 IT 学生成为 IT0449。请帮我解决一下这个。看到这个……一定是这样的……

...
cs0439
it0441
cs0440
it0442
cs0441
cs0442

请让我知道字母数字身份的更好选择

Answer 1

您可以尝试使用 INSTEAD OF INSERT 触发器，如下所示:

create table students(id varchar(10) primary key not null, Department varchar(2), Name varchar(80))
go

create trigger students_insert_PK
    on students
    INSTEAD OF INSERT
as
    declare @id int;
    declare @dept varchar(2); select @dept=Department from INSERTED;
    select @id=cast(max(right(id,4)) as int) from students where Department=@dept;
    set @id=isnull(@id,0)+1;

    insert into students
    select Department+right('0000'+cast(@id as varchar(4)),4)
    , Department
    , name
    from INSERTED;
go

insert into students (Department,Name) values ('CS','John');
insert into students (Department,Name) values ('CS','Pat');
insert into students (Department,Name) values ('CS','Sheryl');
insert into students (Department,Name) values ('IT','Phil');
insert into students (Department,Name) values ('EE','Frank');
insert into students (Department,Name) values ('EE','Amy');
insert into students (Department,Name) values ('EE','Stu');
go

select * from students;
go

结果: