select max(org_no)+1 orgNo from uap_organization p where p.parent_id = '1' 查询结果1003
select to_char(max(org_no)+1, 'fm000000') orgNo from uap_organization p where p.parent_id = '1'
这个写法更好,可以不用管在哪个层级 select to_char(max(org_no)+1, rpad('fm', length(org_no), '0')) orgNo from uap_organization p where p.parent_id = '1'
select to_char(max(org_no)+1, rpad('fm', length(org_no), '0')) orgNo from uap_organization p where p.parent_id = '1' group by org_no 结果:1002 1003 前面的0丢失了
select to_char(max(org_no)+1, rpad('fm', length(org_no) + 2, '0')) orgNo from uap_organization p where p.parent_id = '1'
insert into organization select top 1 (select max(id)+1 from organization) ,(select isnull(max(org_no),'') from organization where id=@pid)+right('000'+convert(varchar,max( convert(int, right(org_no, len(org_no)-len(select max(org_no) from organization where id=@pid) ) ) )+1 ), len(select max(org_no) from organization where id=@pid)+3 ) ,@orgname ,@pid from organization where parent_id=@pid
获取父节点的编码,然后在拼当前节点的编码就好了,不太明白你的意思。select to_char(1, 'fm000') from dual; 这个语句生成的结果就是001,自己看看怎么弄吧
from (select level id from dual connect by level < 3) t1
connect by level < 4;
或者
直接用变量
V_ID := parent_id || child_id;
(
id INTEGER not null,
org_code VARCHAR2(32) default NULL,
org_name VARCHAR2(128) default NULL,
org_no VARCHAR2(32) default NULL,
parent_id INTEGER default NULL,
constraint PK_ORGANIZATION primary key (id)
);这是表结构,转成整形再转字符型好像也是0丢了
查询结果1003
select to_char(max(org_no)+1, rpad('fm', length(org_no), '0')) orgNo from uap_organization p where p.parent_id = '1'
结果:1002 1003 前面的0丢失了
insert into organization
select top 1 (select max(id)+1 from organization)
,(select isnull(max(org_no),'') from organization where id=@pid)+right('000'+convert(varchar,max(
convert(int,
right(org_no,
len(org_no)-len(select max(org_no) from organization where id=@pid)
)
)
)+1
),
len(select max(org_no) from organization where id=@pid)+3
)
,@orgname
,@pid
from organization
where parent_id=@pid