SQl问题两个帖子一起结贴一共200分
http://topic.csdn.net/u/20071018/14/e713a82e-1b2d-4043-a87d-691f04ad1bde.html（给出答案留个名一起给分）
可能我的说明有错误 再次说明
表a生成 id 和 pwd(随机数字)
关键是id号
重0开始到99999
不到5为的数字补0全部重新写入
如此，表里面会有数据：
1
01
001
0001
00001
0
00
000
0000
00000
以此类推 ，最大数为99999
[解决办法]
select right('00000' + cast(col as int),5) col from tb
[解决办法]
create table tb(col varchar(5))insert into tb values('1') insert into tb values('01') insert into tb values('001') insert into tb values('0001') insert into tb values('00001') insert into tb values('0') insert into tb values('00') insert into tb values('000') insert into tb values('0000') insert into tb values('00000')goselect right('00000' + cast(cast(col as int) as varchar),5) col from tbdrop table tb/*col ---------- 00001000010000100001000010000000000000000000000000（所影响的行数为 10 行）*/
[解决办法]
select top 10000 id=identity( bigint,0,1) into tmp from syscolumns a,syscolumns bselect right('00000' + cast(id as varchar) , 5) id from tmpdrop table tmp/*id ---------- 0000000001000020000300004000050000600007*/
[解决办法]
表里有什么数据
要得到什么样的数据
表里有
1
01
001
0001
00001
是不是要得到
00001
00001
00001
00001
00001
[解决办法]
update 表 set id=right('00000' + cast(id as varchar),5)
如果ID 字段为 INT
alter table 表 alter column id varchar(6)
update 表 set id=right('00000' + cast(id as varchar),5)
[解决办法]
create table #(id varchar(6),pwd varchar(10))insert into # select '0',left(checksum(newid()),6) union all select '1',left(checksum(newid()),6)insert into # select left('0'+id,5) as id,left(checksum(newid()),6) from #union allselect left('00'+id,5),left(checksum(newid()),6) from #union allselect left('000'+id,5),left(checksum(newid()),6) from #union allselect left('0000'+id,5),left(checksum(newid()),6) from #union allselect left('00000'+id,5),left(checksum(newid()),6) from #select * from #
[解决办法]
--要插入新记录？declare @a table (id varchar(5))declare @b table (id varchar(5))insert @a select '0'+id id from [Table] where len(id)<5insert [Table](id)select id from @ainsert @b select id from @awhile @@rowcount>0begin delete from @a insert @a select '0'+id id from @b where len(id)<5 insert [Table](id)select id from @a delete from @b insert @b select id from @aend
[解决办法]
create function [dbo].[zizeng](@id int)returns nvarchar(20) begin declare @str nvarchar(20) declare @str1 nvarchar(2) declare @str2 nvarchar(3) declare @str3 nvarchar(4) declare @str4 nvarchar(5) if len(@id)=1 begin set @str1='0'+convert(nvarchar(1),@id) set @str2='00'+convert(nvarchar(1),@id) set @str3='000'+convert(nvarchar(1),@id) set @str4='0000'+convert(nvarchar(1),@id) end else if len(@id)=2 begin set @str2='0'+convert(nvarchar(2),@id) set @str3='00'+convert(nvarchar(2),@id) set @str4='000'+convert(nvarchar(2),@id) end else if len(@id)=3 begin set @str3='0'+convert(nvarchar(3),@id) set @str4='00'+convert(nvarchar(3),@id) end else if len(@id)=4 begin set @str4='0'+convert(nvarchar(4),@id) end else begin set @str4=convert(nvarchar(5),@id) end set @str=@str1+','+@str2+','+@str3+','+@str4return @strend
[解决办法]
create procedure [dbo].huan( @id int)asbegin declare @str1 nvarchar(2) declare @str2 nvarchar(3) declare @str3 nvarchar(4) declare @str4 nvarchar(5) if len(@id)=1 begin set @str1='0'+convert(nvarchar(1),@id) set @str2='00'+convert(nvarchar(1),@id) set @str3='000'+convert(nvarchar(1),@id) set @str4='0000'+convert(nvarchar(1),@id) end else if len(@id)=2 begin set @str2='0'+convert(nvarchar(2),@id) set @str3='00'+convert(nvarchar(2),@id) set @str4='000'+convert(nvarchar(2),@id) end else if len(@id)=3 begin set @str3='0'+convert(nvarchar(3),@id) set @str4='00'+convert(nvarchar(3),@id) end else if len(@id)=4 begin set @str4='0'+convert(nvarchar(4),@id) end else begin set @str4=convert(nvarchar(5),@id) end declare @a table (id nvarchar(20))insert into @a select @str1insert into @a select @str2insert into @a select @str3insert into @a select @str4select * from @aend
[解决办法]
楼主要的结果应该是这样的格式 但是我做不来000000000000000101001000100001202002000300003........12012001200012........100010000100........100001000........1000010001.....99999