求一编号处理解决方案

Question

我的数据库字段ID是自动增长的，但是客户要求把这个ID显示出来，并且按四位显示。比如1，显示出来就是0001。我知道toString()好像是可以，但是不知道具体怎么实现。请各路高手指点一二.....

Answer 1

可以通过functino来解决

代码

use 
TestDb 
go


Create function GetTop4CharByPKID
(@PKID int)
returns nchar(4) 
as 
Begin
Declare @returnchar nchar(4)
set @returnchar=(select 
case  
when @PKID between 1 and 9 then '000'+cast(@PKID as nchar(4))
when @PKID between 10 and 99 then '00'+cast(@PKID as nchar(4))
when @PKID between 100 and 999 then '0'+cast(@PKID as nchar(4))
when @PKID between 999 and 9999 then '0'+cast(@PKID as nchar(4))
when @PKID >9999 then '....'
end 
)
return @returnchar
end

go

select dbo.GetTop4CharByPKID(1) as Number
union all
select dbo.GetTop4CharByPKID(99) as Number
union all
select dbo.GetTop4CharByPKID(100) as Number
union all
select dbo.GetTop4CharByPKID(599) as Number
union all
select dbo.GetTop4CharByPKID(50229) as Number

 

执行结果：

Number
0001
0099
0100
0599
....