使用sql自动生成自定义id

发布于 2024-12-07 05:07:39 字数 715 浏览 0 评论 0原文

我对 sql 和数据库比较陌生,希望在以下主题中获得一些帮助。 我有下表:(少年男性 -> jm,少年女性 -> jf,成年男性 -> am,成年女性 -> af)

id    code     name    
1     jm       john       
2     am       patrick    
3     af       jane       
4     jm       peter      
5     jm       derrick    
6     af       mary       
7     jf       jessica  

并且想在表格内创建一个字段作为参考,该字段是以这种方式由代码和自动增量数字组成:

id      code     name     reference

1     jm       john       jm001

2     am       patrick    am001

3     af       jane       af001

4     jm       peter      jm002

5     jm       derrick    jm003

6     af       mary       af002

7     jf       jessica    jf001

任何人都可以给我一些关于如何做到这一点的提示吗?谢谢

I am relatively new to sql and databases and would like some help in the following topic.
I have the following table: (junior male -> jm, junior female -> jf, adult male -> am, adult female-> af)

id    code     name    
1     jm       john       
2     am       patrick    
3     af       jane       
4     jm       peter      
5     jm       derrick    
6     af       mary       
7     jf       jessica  

and would like to create a field inside the table as a reference which is made up of the code and autoincrement digits in this way:

id      code     name     reference

1     jm       john       jm001

2     am       patrick    am001

3     af       jane       af001

4     jm       peter      jm002

5     jm       derrick    jm003

6     af       mary       af002

7     jf       jessica    jf001

can anyone give me some tips on how to do this? Thank you

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

記柔刀 2024-12-14 05:07:39

首先,问自己“为什么?”正如马丁的评论。如果您对这个问题有一个很好的答案,那么您可能正在寻找每种类型的序列。例如在Oracle中:

CREATE SEQUENCE jm_seq START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE;
CREATE SEQUENCE jf_seq START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE;
CREATE SEQUENCE am_seq START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE;
CREATE SEQUENCE af_seq START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE;

然后,您可以在输入特定类型时使用这些。例如,要输入初级男性,您可以使用:

Insert into table_name 
  values (id_seq.nextVal, 'jm', 'Bart', 'jm' || jm_seq.nextVal);

此外,这不会像示例中那样为您提供前导零,因此如果需要,请考虑格式化您的号码。

First, ask yourself "Why?" as in Martin's comment. If you have a good answer to this question, then a sequence for each type is probably what you are looking for. For example in Oracle:

CREATE SEQUENCE jm_seq START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE;
CREATE SEQUENCE jf_seq START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE;
CREATE SEQUENCE am_seq START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE;
CREATE SEQUENCE af_seq START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE;

Then, you can use these when you enter the specific types. For example, to enter a junior male, you'd use:

Insert into table_name 
  values (id_seq.nextVal, 'jm', 'Bart', 'jm' || jm_seq.nextVal);

Also, this will not give you leading zeros as in your example, so look into formatting your number if that is needed.

怪我入戏太深 2024-12-14 05:07:39

您可以使用以下代码来达到您想要的结果...干杯...

drop table if exists Custom_id_Dynamic;
create table Custom_id_Dynamic (id int primary key auto_increment , Code varchar(20), 
Name varchar(20), Reference varchar(20));


drop procedure if exists Dynamic_id;

set @index:=0;
set @jm:=0;
set @unknown:=0;
set @am:=0;
set @af:=0;
set @jf:=0;

delimiter $

create procedure Dynamic_id(
in code_new varchar(20), in name_new varchar(20)
)

begin
set code_new=lower(code_new);
if code_new='jm' then set @jm:=@jm+1, @index:=@jm;
elseif code_new='am' then set @am:=@am+1, @index:=@am;
elseif code_new='af' then set @af:=@af+1, @index:=@af;
elseif code_new='jf' then set @jf:=@jf+1, @index:=@jf;
else set @unknown:=@unknown+1, @index:=@unknown;
end if;
insert into Custom_id_Dynamic (Code, Name, Reference) 
values (code_new, name_new, concat(code_new, lpad(@index,3,0)));
end;



call Dynamic_id('jm','john');
call Dynamic_id('am','patrick');
call Dynamic_id('af','jane');
call Dynamic_id('jm','peter');
call Dynamic_id('jm','derrick');
call Dynamic_id('af','mary');
call Dynamic_id('jf','jessica');


select * from Custom_id_Dynamic;

You can make use of this following Code in-order to arrive to your desired result... Cheers...

drop table if exists Custom_id_Dynamic;
create table Custom_id_Dynamic (id int primary key auto_increment , Code varchar(20), 
Name varchar(20), Reference varchar(20));


drop procedure if exists Dynamic_id;

set @index:=0;
set @jm:=0;
set @unknown:=0;
set @am:=0;
set @af:=0;
set @jf:=0;

delimiter $

create procedure Dynamic_id(
in code_new varchar(20), in name_new varchar(20)
)

begin
set code_new=lower(code_new);
if code_new='jm' then set @jm:=@jm+1, @index:=@jm;
elseif code_new='am' then set @am:=@am+1, @index:=@am;
elseif code_new='af' then set @af:=@af+1, @index:=@af;
elseif code_new='jf' then set @jf:=@jf+1, @index:=@jf;
else set @unknown:=@unknown+1, @index:=@unknown;
end if;
insert into Custom_id_Dynamic (Code, Name, Reference) 
values (code_new, name_new, concat(code_new, lpad(@index,3,0)));
end;



call Dynamic_id('jm','john');
call Dynamic_id('am','patrick');
call Dynamic_id('af','jane');
call Dynamic_id('jm','peter');
call Dynamic_id('jm','derrick');
call Dynamic_id('af','mary');
call Dynamic_id('jf','jessica');


select * from Custom_id_Dynamic;
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文