sql 随机密码生成代码,求指教

不穿内裤的超人 2016-07-19 03:22:35

create table #tmp(row int)

declare @rand1 decimal(15,15),@rand2 decimal(15,15),@rand3 decimal(15,15)

declare @in1 int ,@in2 int,@in3 int

declare @ch1 char(6) ,@ch2 char(6),@ch3 char(6),@st char(40)

select @in1=substring(DateName(mcs,GetDate()),3,1)

select @in2=substring(DateName(mcs,GetDate()),2,1)

select @in3=substring(DateName(mcs,GetDate()),1,1)

select @rand1=RAND(),@rand2=RAND(),@rand3=RAND()

select @ch1=substring(cast(@rand1 as CHAR(20)),@in1+3,6),@ch2=substring(cast(@rand2 as CHAR(20)),@in2+3,6),@ch3=substring(cast(@rand3 as CHAR(20)),@in3+3,6)

insert into #tmp(row) select SUBSTRING(@ch1,1,2)

insert into #tmp(row) select SUBSTRING(@ch1,2,2)

insert into #tmp(row) select SUBSTRING(@ch1,3,2)

insert into #tmp(row) select SUBSTRING(@ch1,4,2)

insert into #tmp(row) select SUBSTRING(@ch1,5,2)

insert into #tmp(row) select SUBSTRING(@ch2,1,2)

insert into #tmp(row) select SUBSTRING(@ch2,5,2)

insert into #tmp(row) select SUBSTRING(@ch2,3,2)

insert into #tmp(row) select SUBSTRING(@ch2,4,2)

insert into #tmp(row) select SUBSTRING(@ch2,5,2)

insert into #tmp(row) select SUBSTRING(@ch3,2,2)

insert into #tmp(row) select SUBSTRING(@ch3,3,2)

insert into #tmp(row) select SUBSTRING(@ch3,4,2)

insert into #tmp(row) select SUBSTRING(@ch3,5,2)

--select * from #tmp





create table #cte(row int,value char(2))

insert into #cte(row,value) 

(

select '1','A' union all  select '2','B' union all  select '3','C' union all  select '4','D' union all  select '5','E' union all 

select '6','F' union all  select '7','G' union all  select '8','H' union all  select '9','I' union all  select '10','G' union all 

select '11','K' union all  select '12','L' union all  select '13','M' union all  select '14','N' union all  select '15','O' union all 

select '16','P' union all  select '17','Q' union all  select '18','R' union all  select '19','S' union all  select '20','T' union all 

select '21','U' union all  select '22','V' union all  select '23','W' union all  select '24','X' union all  select '25','Y' union all 

select '26','Z' union all  select '27','1' union all  select '28','2' union all  select '29','3' union all  select '30','4' union all 

select '31','5' union all  select '32','6' union all  select '33','7' union all  select '34','8' union all  select '35','9' union all 

select '36','a' union all  select '37','b' union all  select '38','c' union all  select '39','d' union all  select '40','e' union all 

select '41','f' union all  select '42','g' union all  select '43','h' union all  select '44','i' union all  select '45','j' union all 

select '46','k' union all  select '47','l' union all  select '48','m' union all  select '49','n' union all  select '50','o' union all 

select '51','p' union all  select '52','q' union all  select '53','r' union all  select '54','s' union all  select '55','t' union all 

select '51','u' union all  select '52','v' union all  select '53','w' union all  select '54','x' union all  select '55','y' union all 

select '61','z' union all  select '62','1' union all  select '63','2' union all  select '64','3' union all  select '65','4' union all 

select '66','5' union all  select '67','6' union all  select '68','7' union all  select '69','8' union all  select '70','9' union all 

select '71','!' union all  select '72','@' union all  select '73','#' union all  select '74','%' union all  select '75','(' union all 

select '76',')' union all  select '77','_' union all  select '78','+' union all  select '79','=' union all  select '80','-' union all 

select '81','{' union all  select '82','}' union all  select '83','[' union all  select '84',']' union all  select '85',':' union all 

select '86',';' union all  select '87','z' union all  select '88','.' union all  select '89','?' union all  select '90','1' 

)

create table #st(st char(40))



select ''+value from #cte,#tmp where #cte.row=#tmp.row order by value FOR XML PATH('') 



drop table #tmp

drop table #cte

drop table #st