with tb as ( select top 1 convert(varchar,number)number from master..spt_values where type='p' and number between 0 and 9 order by NEWID() ) select 'CCF'+a.number+b.number+c.number+d.number+e.number from tb a,tb b,tb c,tb d,tb e
select 'CCF'+right('00000'+rtrim(cast(rand()*100000 as int)),5)
为什么改成top 3000就会出100000行的?
declare @tb table(txt varchar(8)) declare @s int set @s=0 while(@s<10000) begin insert into @tb values('CCF'+right('00000'+rtrim(@s),5)) set @s=@s+1 end select top 3000 * from @tb order by newid()
with tb as (
select top 1 convert(varchar,number)number from master..spt_values where type='p' and number between 0 and 9 order by NEWID()
)
select 'CCF'+a.number+b.number+c.number+d.number+e.number from tb a,tb b,tb c,tb d,tb e
declare @s int
set @s=0
while(@s<10000)
begin
insert into @tb values('CCF'+right('00000'+rtrim(@s),5))
set @s=@s+1
end
select top 3000 * from @tb order by newid()