--生成100个10位的字符串
declare @tmp varchar(100),@c int,@w int,@M int
set @tmp=''
set @c=1
while @c<=100
begin
set @W=0
while @w<=10
begin
select @tmp=@tmp+rnum from (select top 1 rnum from #3 order by newid())A
set @w=@w+1
end
select @M=count(1) from codes where code=@tmp
if @M=0
begin
insert codes(code) select @tmp
set @tmp=''
set @c=@c+1
Endend
declare @tmp varchar(100),@c int,@w int,@M int
set @tmp=''
set @c=1
while @c<=100
begin
set @W=0
while @w<=10
begin
select @tmp=@tmp+rnum from (select top 1 rnum from #3 order by newid())A
set @w=@w+1
end
select @M=count(1) from codes where code=@tmp
if @M=0
begin
insert codes(code) select @tmp
set @tmp=''
set @c=@c+1
Endend
---生成测试数据
create table #3
(
[rnum] varchar(50)
)
declare @i int
set @i=ascii('A')
while @i<=ascii('Z')
begin
insert #3 select char(@i)
set @i=@i+1
end
select top 0 rnum as code into codes from #3--生成100个10位的字符串
declare @tmp varchar(100),@c int,@w int,@M int
set @tmp=''
set @c=1
while @c<=100
begin
set @W=0
while @w<=10
begin
select @tmp=@tmp+rnum from (select top 1 rnum from #3 order by newid())A
set @w=@w+1
end
select @M=count(1) from codes where code=@tmp
if @M=0
begin
insert codes(code) select @tmp
set @tmp=''
set @c=@c+1
Endend
select * from codes
---结果就不帖多了
KSLBCKAAGRW
OUKBWMUVXKA
KNTKXPMOERT
MHKDBKGZXRK
ZDPNRJDGZZY
MKLBDPGFMOF
WXEHWCJORNT
BTHVTMUJDRL
ISQIDZFFXNE
NCPZKZUUQQQ
CFKDFQOBAVG
ELVLDIDGYLN
WMDUJTROGLG
UKXBKNMDCZV
FQLJIVSBSSI
GUADNIARIBV
QILWHTTGTQQ
FHJBTPKIOCO
WXKSIHMXHLE
EJDMWDMOBGG
CYCPFXCKXNI
LGZZGQVGMXS
QHWNTIEHLSI
IVDGVLMVQWZ
GKQOJUWYAOP
drop table #3
(
[rnum] varchar(50)
)
declare @i int
set @i=ascii('A')
while @i<=ascii('Z')
begin
insert #3 select char(@i)
set @i=@i+1
end
godeclare @tmp varchar(100),@w int
set @w=1
while @w<=100
begin
set @tmp=''
select @tmp=@tmp+rnum from (select top 10 rnum from #3 order by newid())A
insert codes(code) select @tmp
set @w=@w+1
end
select * from codes
go
drop table #3,codes
create table #3
(
[rnum] varchar(50)
)
declare @i int
set @i=ascii('A')
while @i<=ascii('Z')
begin
insert #3 select char(@i)
set @i=@i+1
end
select top 0 rnum as code into codes from #3--生成100个10位的字符串
declare @tmp varchar(100),@c int,@w int,@B int,@M int,@N intset @M=100 ---楼主要的m
set @N=10 ---楼主要的N
set @tmp=''
set @c=1
while @c<=@M
begin
set @W=0
while @w<=@N
begin
select @tmp=@tmp+rnum from (select top 1 rnum from #3 order by newid())A
set @w=@w+1
end
select @B=count(1) from codes where code=@tmp
if @B=0
begin
insert codes(code) select @tmp
set @tmp=''
set @c=@c+1
Endend
而且没有判断是否重复
create table #3
(
[rnum] varchar(50)
)
declare @i int
set @i=ascii('A')
while @i<=ascii('Z')
begin
insert #3 select char(@i)
set @i=@i+1
end--生成100个10位的字符串
declare @tmp varchar(100),@c int,@w int
set @tmp=''
set @w=1--新加
set @c=1
while @c<=100
beginwhile @w<=10
begin
select @tmp=@tmp+rnum from (select top 1 rnum from #3 order by newid())A
set @w=@w+1
end insert codes(code) select @tmp
set @tmp=''
set @c=@c+1
set @w=1 --新加end
select * from codes
drop table #3,codes
--@Num:字符个数
create proc Test(@Count int,@Num int)
as
Declare @I int --生成字符的序号
Declare @EachStr varchar(200)--得到每个字符串
--set @Num=10
--set @count=20
set nocount on
---生成测试数据
if not object_id('tempdb..#3') is null
drop table tempdb..#3
if not object_id('codes') is null
drop table codescreate table #3
(
[rnum] varchar(50)
)
set @i=ascii('A')
while @i<=ascii('Z')
begin
insert #3 select char(@i)
set @i=@i+1
endset @i=@num/26
while @i>0
begin
insert into #3
select [rnum] from #3 set @i=@i-1
endcreate Table Codes(code varchar(200))
CREATE CLUSTERED
INDEX [ss] ON codes ([code])--开始生成字符串
set @I=1
while @I<=@count
begin
set @eachstr=''
select @eachstr=@eachstr+rnum from (select top 200 [rnum] from #3 order by newid())a
set @eachstr=substring(@eachstr,1,@num) if not exists(Select 1 from codes where code=@eachstr)
begin
insert into codes
select @eachstr
set @i=@i+1
end
end --显示生成字符串
select * from codes
set nocount off--删除临时表
drop table codes
drop table #3GO
declare @i int
set @i=ascii('A')
while(@i<=ascii('Z'))
begin
insert into @zmb values(char(@i))
set @i=@i+1
end--生成100个10个字符的字符串
declare @n int,@m int
declare @tb table(zfc char(10))
declare @zfch varchar(10)
set @n=1
set @m=1
set @zfch=''
set nocount on
while @n<=100
begin
while @m<=10
begin
set @zfch=@zfch+(select top 1 zm from @zmb order by newid())
set @m=@m+1
end
insert into @tb values(@zfch)
set @zfch=''
set @m=1
set @n=@n+1
end
set nocount off
select * from @tb
To tangqijun199 and wgsasd311:谢谢你们,问题已经解决。最后我用的方法:生成100个10位的随机不重复字母字符串方法一--生成100个10位的随机不重复字符串
declare @tmp varchar(100),@c int,@w int
set @tmp=''
set @c=1while @c<=100begin
set @w=1
while @w<=10
begin
set @tmp=@tmp+(char (cast ((RAND(CHECKSUM(NEWID()))*26+1) as int)+64))
set @w=@w+1
end insert codes(code) select @tmp
set @tmp=''
set @c=@c+1end--查询生成数据
select * from codes
select distinct code from codes--删除数据
delete from codes
生成100个10位的随机不重复字母字符串方法二-- 建立临时表,存放字符A-Z
create table #3
(
[rnum] varchar(50)
)
declare @i int
set @i=ascii('A')
while @i<=ascii('Z')
begin
insert #3 select char(@i)
set @i=@i+1
end
--生成100个10位的随机不重复字符串
declare @tmp varchar(100),@c int,@w int,@M int
set @tmp=''
set @c=1
while @c<=100
begin
set @w=1
while @w<=10
begin
select @tmp=@tmp+rnum from (select top 1 rnum from #3 order by newid())A
set @w=@w+1
end
select @M=count(1) from codes where code=@tmp
if @M=0
begin
insert codes(code) select @tmp
set @tmp=''
set @c=@c+1
Endend
--查询生成数据
select * from codes
select distinct code from codes--删除临时表和数据
drop table #3
delete from codes