先执行 Create table #1 (dd varchar(7)) Create table a (idd varchar(3), de varchar(4)) 再一直执行多遍 declare @count int Select @count = count(*) from a if @count > 0 begin declare @de1 varchar(10), @de varchar(10), @idd varchar(3) select @de = de, @idd = idd from a set @de1 = substring(convert(varchar(4), datepart(yy, getdate())), 3, 4)+convert(varchar(2), datepart(dd, getdate())) if @de <> @de1 Update a set de = @de1, idd = '001' else begin declare @idd1 int, @idd2 int set @idd1 = convert(int, @idd)+1 set @idd2 = len(@idd1) if @idd2 = 1 Update a set idd = '00' +convert(varchar(3), @idd1) if @idd2 = 2 Update a set idd = '0' +convert(varchar(3), @idd1) if @idd2 = 3 Update a set idd = convert(varchar(3), @idd1) end end else begin insert a (idd, de) values ('001', substring(convert(varchar(4), datepart(yy, getdate())), 3, 4)+convert(varchar(2), datepart(dd, getdate()))) end Select * from aInsert #1 (dd) Select de+idd from a Select * from #1测试过了没问题
//--动态编号 function TCustomerFrm.GetCardId:String; var CurDate,CurYear,CurMonth:string; Query:TADOQuery; begin Curdate := leftstr(datetimetostr(now),pos(' ',datetimetostr(now))); CurYear := leftstr(curdate,4); CurMonth := leftstr(rightstr(curdate,length(curdate)-pos('-',curdate)),pos('-',rightstr(curdate,length(curdate)-pos('-',curdate)-1))); if length(Curmonth) = 1 then Curmonth := '0' + Curmonth; Query := TADOQuery.Create(self); Query.Connection := DM.ADOConnDB; Query.SQL.Text:='select max(PID) as PID from Person' + ' where left(PID,6)=''' + curyear + curmonth + ''''; query.Open; if query.FieldByName('PID').AsString = '' then begin result := curyear + curmonth + '001'; end else if query.FieldByName('PID').AsString <> '' then begin if RightStr(IntToStr(query.FieldByName('PID').AsString) + 1),3) = '999' then result := curyear + curmonth + '001' //=====如编号等200504999时则为200504001 else result := inttostr(strtoint(query.FieldByName('PID').AsString) + 1); //====否则自加1 end; query.Close; query.Free; end;
Create table #1 (dd varchar(7))
Create table a (idd varchar(3), de varchar(4))
再一直执行多遍
declare @count int
Select @count = count(*) from a
if @count > 0
begin
declare @de1 varchar(10), @de varchar(10), @idd varchar(3)
select @de = de, @idd = idd from a
set @de1 = substring(convert(varchar(4), datepart(yy, getdate())), 3, 4)+convert(varchar(2), datepart(dd, getdate()))
if @de <> @de1
Update a set de = @de1, idd = '001'
else
begin
declare @idd1 int, @idd2 int
set @idd1 = convert(int, @idd)+1
set @idd2 = len(@idd1)
if @idd2 = 1
Update a set idd = '00' +convert(varchar(3), @idd1)
if @idd2 = 2
Update a set idd = '0' +convert(varchar(3), @idd1)
if @idd2 = 3
Update a set idd = convert(varchar(3), @idd1)
end
end
else
begin
insert a (idd, de) values ('001', substring(convert(varchar(4), datepart(yy, getdate())), 3, 4)+convert(varchar(2), datepart(dd, getdate())))
end
Select * from aInsert #1 (dd) Select de+idd from a
Select * from #1测试过了没问题
function TCustomerFrm.GetCardId:String;
var
CurDate,CurYear,CurMonth:string;
Query:TADOQuery;
begin
Curdate := leftstr(datetimetostr(now),pos(' ',datetimetostr(now)));
CurYear := leftstr(curdate,4);
CurMonth := leftstr(rightstr(curdate,length(curdate)-pos('-',curdate)),pos('-',rightstr(curdate,length(curdate)-pos('-',curdate)-1)));
if length(Curmonth) = 1 then Curmonth := '0' + Curmonth;
Query := TADOQuery.Create(self);
Query.Connection := DM.ADOConnDB;
Query.SQL.Text:='select max(PID) as PID from Person' +
' where left(PID,6)=''' + curyear + curmonth + '''';
query.Open;
if query.FieldByName('PID').AsString = '' then
begin
result := curyear + curmonth + '001';
end
else if query.FieldByName('PID').AsString <> '' then
begin
if RightStr(IntToStr(query.FieldByName('PID').AsString) + 1),3) = '999' then
result := curyear + curmonth + '001' //=====如编号等200504999时则为200504001
else
result := inttostr(strtoint(query.FieldByName('PID').AsString) + 1); //====否则自加1
end;
query.Close;
query.Free;
end;
因为,你的时间做流号就是判断之前用过的日期和现在的日期是否是同一天
如果不是同一天那么号码就需要从001从新开始
所以我的办法是,建一个表,在里面装入日期也就是0504,流水号001两个字段当你的生产单加一条生产通知单的时候,就需要判断日期是否相同,
如果相同UPDATE
因为,你的时间做流号就是判断之前用过的日期和现在的日期是否是同一天
如果不是同一天那么号码就需要从001从新开始
所以我的办法是,建一个表,在里面装入日期也就是0504,流水号001两个字段当你的生产单加一条生产通知单的时候,就需要判断日期是否相同,
如果相同UPDATE流水号+1,如果不相同则update日期为当天的日期
流水号为001这样做才是标准的。
因为我以前也做过这方面的。。
ERP
???
你看清楚了吗?
号码是固定的。7位,照你那样加不知道加多少了
而且前4位是日期,我晕死能。