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
1
01
001
0001
00001 0
00
000
0000
00000以此类推 ,最大数为99999
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
----------
00001
00001
00001
00001
00001
00000
00000
00000
00000
00000(所影响的行数为 10 行)
*/
不足5位补0的:
select replicate('0',(5-len(id)))+cast(id as varchar) from a
id
----------
00000
00001
00002
00003
00004
00005
00006
00007
*/
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 col =
case when len(col) = 1 then '0000' + col
when len(col) = 2 then '000' + col
when len(col) = 3 then '00' + col
when len(col) = 4 then '0' + col
else col
end
from tbdrop table tb
/*
col
---------
00001
00001
00001
00001
00001
00000
00000
00000
00000
00000(所影响的行数为 10 行)
*/
不是补0 是不到5位的id重新加入一条数据
拿数字1举例
表里面会有id数据: 1-99999
1
01
001
0001
00001 to:潇洒老乌龟 你还没有仔细看题目http://topic.csdn.net/u/20071018/11/886f50a2-a483-4dd9-9d53-eb97cd0a8804.html (给出答案留个名一起给分) 最上面的地址留错了
select top 5 identity(int,0,1) id,newid() pwd into # from syscolumns a,syscolumns b
select '0-'+right('00000'+convert(varchar(5),convert(int,right(id,5))+1),5) as id,pwd from #
(
@ID varchar(5),
@PWd varchar(20)
)
AS
Begin
Insert a values(@ID,@PWD)
While len(@ID)<5
Begin
Set @ID='0'+@ID
Insert a values(@ID,@PWD)
End
End
alter table 表 alter column id varchar(6)
update 表 set id=right('00000' + cast(id as varchar),5)
数据表里面就会有0,00,000,0000,00000,1,01,001,0001,00001等
就是每个数字补一个0一直补到满5位的数值 前面不满5位的数值也会存在与表里http://topic.csdn.net/u/20071018/11/886f50a2-a483-4dd9-9d53-eb97cd0a8804.html (给出答案留个名一起给分)
update [table] set id='0'+id where len(id)<5
数据表里面就会有0,00,000,0000,00000,1,01,001,0001,00001等
就是每个数字补一个0一直补到满5位的数值 前面不满5位的数值也会存在与表里 http://topic.csdn.net/u/20071018/11/886f50a2-a483-4dd9-9d53-eb97cd0a8804.html (给出答案留个名一起给分)
select replicate('0',(5-len(id)))+id ,pwd from a
where len(id)<5
或者
insert into a
select right('00000'+id,5),pwd from a
where len(id)<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 all
select left('00'+id,5),left(checksum(newid()),6) from #
union all
select left('000'+id,5),left(checksum(newid()),6) from #
union all
select left('0000'+id,5),left(checksum(newid()),6) from #
union all
select left('00000'+id,5),left(checksum(newid()),6) from #
select * from #
id pwd
------ ----------
0 328923
1 265936
00 -12479
01 -80121
000 161549
001 -11927
0000 -16952
0001 -30864
00000 777012
00001 -16201
00000 170690
00000 -16950
Set Id='0'+Id
Where Len(Id)<5
insert into #
select left('0'+id,5) as id,left(checksum(newid()),6) from #
union all
select left('00'+id,5),left(checksum(newid()),6) from #
union all
select left('000'+id,5),left(checksum(newid()),6) from #
union all
select left('0000'+id,5),left(checksum(newid()),6) from #
Set Id= '0'+Id
Where Len(Id) <5
Group By 作者
Order By 得票数 Desc
while @@rowcount>0
update [table] set id= '0'+id where len(id) <5
0
00
000
0000
00000 这些id都会出现在表里面 表里现在id已经存在了0-99999的数字把没有5位的数字 重补一个0一直补到满5位 这些id都还是存在表里以12为例
表里会有id:
12
012
0012
00012 数字满5位为止
select top 100000 identity(int,0,1) id,left(newid(),6) pwd into # from syscolumns a,syscolumns binsert into #
select left('0'+id,5) as id,left(checksum(newid()),6) from #
union all
select left('00'+id,5),left(checksum(newid()),6) from #
union all
select left('000'+id,5),left(checksum(newid()),6) from #
union all
select left('0000'+id,5),left(checksum(newid()),6) from #
declare @a table (id varchar(5))
declare @b table (id varchar(5))insert @a select '0'+id id from [Table] where len(id)<5
insert [Table](id)select id from @a
insert @b select id from @a
while @@rowcount>0
begin
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 @a
end
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+','+@str4
return @str
end
table(id varchar(5),pwd varchar(20))
里面有数据
12,null
0,null
0001,null
11111,null
现在id要变成
12,null
012,null
0012,null
00012,null0,null
00,null
000,null
0000,null
00000,null0001,null
00001,null11111,null其中pwd要插入随机不重复的5位数
(
@id int
)
as
begin
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 @str1
insert into @a select @str2
insert into @a select @str3
insert into @a select @str4
select * from @a
end
go
declare @i as int
set @i=1
while @I<=99
begin
declare @sql1 as nvarchar(4000)
set @sql1='insert into table1(a,b)values('+char(39)+cast(@i as varchar)+char(39)+','+char(39)+cast((@i-1) as varchar)+char(39)+')'
print @sql1
--exec(@sql1)
declare @j as int
set @j=1
while @j<5
begin
declare @sql2 as nvarchar(4000)
set @sql2='insert into table1(a,b) values('+char(39)+substring('00000',1,@j)+cast(@i as varchar)+char(39)+','+char(39)+cast(@i-1 as varchar)+char(39)+')'
print @sql2
--exec(@sql1)
set @j=@J+1
end
set @i=@i+1
end
lv_id varchar2(5);
For lv_list in(select id into from a)
Loop
lv_id = lv_list.id
While (Length(lv_id) < 5)
Loop
lv_id := '0' || lv_id;
insert into a(id) values(lv_id);
End Loop;
End Loop;
//========================================
return varchar2
is
lv_id varchar2(5);
begin
-----------------------------------------------
For lv_list in(select id into from a)
Loop
lv_id = lv_list.id;
While (Length(lv_id) < 5)
Loop
lv_id := '0' || lv_id;
insert into a(id) values(lv_id);
End Loop;
End Loop;
-----------------------------------------------
commit;
return 'ok';
end f_a_test;
select top 100000 identity(int,0,1) id,left(newid(),6) pwd into # from syscolumns a,syscolumns balter table # add idd varchar(10)update # set idd=id insert into #
select left(checksum(newid()),6),right('0'+convert(varchar(10),id),5) as idd from # where len(id)<5
union all
select left(checksum(newid()),6),right('00'+convert(varchar(10),id),5) as idd from # where len(id)<4
union all
select left(checksum(newid()),6),right('000'+convert(varchar(10),id),5) as idd from # where len(id)<3
union all
select left(checksum(newid()),6),right('0000'+convert(varchar(10),id),5) as idd from # where len(id)<2 idd pwd
---------- ------------
0 44F742
00 127154
000 188524
0000 151152
00000 -85292
......
99991 A546C9
99992 636494
99993 FA692F
99994 CA26B4
99995 4F0D69
99996 B2A13F
99997 1A35A6
99998 7BEADB
99999 E64D89(所影响的行数为 111110 行)
select 作者,投票
from (select 作者,sum(投票)as 投票
from 表
group by 作者
order by 投票 desc) list
where RowNum <= 100
order by 投票 desc----------------------------------------------------