大家新年好!求助一个自动增加编号的问题.如下:
整个语句:insert   into   ystzd2   (djbh,rq,DM1,DM2,JE)   select     (Select   max(djbh)+1   From   ystzd2)   as   djbh, '2006-08-01   00:00:00.000 ', ' ', ' ', '222 '  
上面的语句达到的自动插入单据编号.  
问题:  
1.djbh是字符型的.初始值是0001.经过插入后就是2了.能不能让它的值经过插入后是0002?  
2.当djbh的值增加到22时.插入的是0022.如此类推   0222   2222  

请问用什么语句能达到解决上面的问题呢?  

祝大家新年快乐.在线等待.谢谢大家...    
 


------解决方案--------------------
insert into ystzd2 (djbh,rq,DM1,DM2,JE) select (Select right( '000 '+ltrim(max(djbh)+1),4) From ystzd2) as djbh, '2006-08-01 00:00:00.000 ', ' ', ' ', '222 '
------解决方案--------------------
insert into ystzd2 (djbh,rq,DM1,DM2,JE)
select
right( '000 '+rtrim(isnull(max(djbh),0)+1),4) as djbh,
'2006-08-01 00:00:00.000 ', ' ', ' ', '222 '
From
ystzd2
------解决方案--------------------
insert into ystzd2 (djbh,rq,DM1,DM2,JE)
Select
right( ' ' + cast(isnull(max(djbh)+1,1) as varchar(100)),4) as djbh,
'2006-08-01 00:00:00.000 ', ' ', ' ', '222 '
From ystzd2

------解决方案--------------------
--改
insert into ystzd2 (djbh,rq,DM1,DM2,JE)
Select
right( '0000 ' + cast(isnull(max(djbh)+1,1) as varchar(100)),4) as djbh,
'2006-08-01 00:00:00.000 ', ' ', ' ', '222 '
From ystzd2


------解决方案--------------------
如果是作为字符比较，当然是是6大。

执行如下SQL看效果：

create table #t (f varchar(100))

insert into #t select '007 '
insert into #t select '6 '

select *
from #t
order by f

drop table #t