根据另一个表的唯一ID自动增加一个表的字母数字ID [英] autoincrementing alphanumeric id for a table based on unique id of another table

问题描述

我想根据学生的部门为学生生成一个唯一的ID。
当部门ID为CS时，我希望学生ID为CS0448；对于部门ID IT，则为IT0448，依此类推。这些ID必须加1。

I want to generate a unique id for a student based on the department of the student. I want student Id to be CS0448 when department id is CS,and IT0448 for department id IT and so on.And these IDs must increment by 1 .

我以为我将拥有另一个身份，并为每个添加的学生将其自动递增1，并将其与部门ID连接起来，但这无济于事。这将导致CS0448旁边添加的IT学生为IT0449。请帮我解决一下这个。

I thought that I will have another identity and autoincrement it by 1 for each student added and concatenate it with department Id,but that wont help me.It will cause IT student added next to CS0448 to be IT0449. Please help me with this. see this...it must be something like this..

...
cs0439
it0441
cs0440
it0442
cs0441
cs0442

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

Please let me know the better options for a alphanumeric identity

推荐答案

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

You could try an INSTEAD OF INSERT trigger, like this:

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

结果：

这篇关于根据另一个表的唯一ID自动增加一个表的字母数字ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！