SQL中生成随机数

 

SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
           + (DATEPART(ss, GETDATE()) * 1000 )
           + DATEPART(ms, GETDATE()) )

select RAND(DATEPART(mm, GETDATE()))
select DATEPART(ss, GETDATE())
select DATEPART(ms, GETDATE())

--随机数：0-9[数字范围]
declare @m as int;
declare @n as int;
set @m=0;
set @n=9;
select   cast(cast((rand()*(@m-@n)+@n) as int) as char(1))

--随机数：5位[数字范围]
declare @m as int;
declare @n as int;
set @m=0;
set @n=9;
select  cast(cast((rand()*(@m-@n)+@n) as int) as char(1))
    + cast(cast((rand()*(@m-@n)+@n) as int) as char(1))
    + cast(cast((rand()*(@m-@n)+@n) as int) as char(1))
    + cast(cast((rand()*(@m-@n)+@n) as int) as char(1))
    + cast(cast((rand()*(@m-@n)+@n) as int) as char(1))

--随机数：1位[字母范围]
declare @a as int;
declare @z as int;
declare @str as char(26);

set @a=1;
set @z=26;
set @str='ABCDEFGHIZKLMNOPQRSTUVWXYZ';
select   substring(@str,cast((rand()*(@a-@z)+@z) as int),1)

--随机数：5位[字母范围]
declare @a as int;
declare @z as int;
declare @str as char(26);

set @a=1;
set @z=26;
set @str='ABCDEFGHIZKLMNOPQRSTUVWXYZ';
select   substring(@str,cast((rand()*(@a-@z)+@z) as int),1)
    + substring(@str,cast((rand()*(@a-@z)+@z) as int),1)
    + substring(@str,cast((rand()*(@a-@z)+@z) as int),1)
    + substring(@str,cast((rand()*(@a-@z)+@z) as int),1)
    + substring(@str,cast((rand()*(@a-@z)+@z) as int),1)

--随机数：8位[数字字母混合]
declare @i as int;
declare @m as int;
declare @n as int;
declare @a as int;
declare @z as int;
declare @str as char(26);
declare @result as varchar(8);

set @i=0;
set @m=0;
set @n=9;
set @a=1;
set @z=26;
set @str='ABCDEFGHIZKLMNOPQRSTUVWXYZ';
set @result='';

while @i < 8
begin
    if(cast((rand()*(1-4)+4) as int)%3=0)
        set @result=@result+substring(@str,cast((rand()*(@a-@z)+@z) as int),1);--字母
    else
        set @result=@result+cast(cast((rand()*(@m-@n)+@n) as int) as char(1));--数字
   
    set @i = @i+1;
end

print(@result);