编号由8位数字组成,
11111001
11111002
11111003
22222001
22222002
22222003
68686001
68686002在数据库中找到前5位相同的编码
然后找到把后三位编码最大的加一,生成一个新的编码谢谢!!!!!
11111001
11111002
11111003
22222001
22222002
22222003
68686001
68686002在数据库中找到前5位相同的编码
然后找到把后三位编码最大的加一,生成一个新的编码谢谢!!!!!
先copy下前面5位到另一个字符串(也就是11111)
在把前5位delete掉(编号变成001)
加一(编号变成002)
再把前面copy下来的字符串(11111)和(002)合并
insert into tabName (id)
values(
select max(id) +1
from tabName
group by substr(to_char(id),1,5)
)
insert into tabName (id)
(
select max(id) + 1
from tabName
group by substr(to_char(id),1,5)
)
SELECT 新号=substring(id,1,5)+(case
when CAST(SUBSTRING(max(id),6,len(id)) as int)+1)<10
then
'00'+cast(CAST(SUBSTRING(max(id),6,len(id)) as int)+1) as char(3))
when between 10 and 100
then
'0'+cast(CAST(SUBSTRING(max(id),6,len(id)) as int)+1) as char(3))
else
cast(CAST(SUBSTRING(max(id),6,len(id)) as int)+1) as char(3))
end)
from 表
group by substring(id,1,5)
(
select max(id) + 1
from tabName
)效果一样
这是以前写的代码
但绝对能实现
var
Num:integer;
i:integer;
begin
frmDataModule.TCustInfo.Last;//最后一条记录
if frmDataModule.TCustInfo.FieldByName('CustNo').Value<>null then
begin
i:=StrToInt(frmDataModule.TCustInfo.FieldByName('CustNo').Value);//最后的记录号
Inc(i);加1
Num:=length(IntToStr(i));
case Num of
1:CustID.Text:='00000'+IntToStr(i);
2:CustID.Text:='0000'+IntToStr(i);
3:CustID.Text:='000'+IntToStr(i);
4:CustID.Text:='00'+IntToStr(i);
5:CustID.Text:='0'+IntToStr(i);
6:CustID.Text:=IntToStr(i);
7..10:Application.MessageBox('数据已经超出范围!请处理不用的数据!','系统提示',MB_OK+MB_ICONINFORMATION+MB_SYSTEMMODAL);
end;
end else
CustID.Text:='000001';//如果是第一条
end;
where id in
(select a.max_a_id from
(select id/1000 a_id ,max(id) max_a_id from table group by id/1000 ) a)