【sql 】怎样生成（由字母或数字组成的）不重复6位随机数

[yuna@liu]

现在需要大约 100万的6位随机数做密码（有数字或者字母组成），不能重复
大家有好的办法吗

[永生天地]

共计用时47分50秒

[东那个升]

CREATE PROCEDURE dbo.uspCreatePassword(    
  @UpperCaseItems SMALLINT--指定含有的大写个数
  ,    @LowerCaseItems SMALLINT--指定含有的小写个数
  ,    @NumberItems SMALLINT--指定含有的数字个数
  ,    @SpecialItems SMALLINT)--指定含有的特殊字符个数
 AS  
 SET NOCOUNT ON 
 DECLARE @UpperCase VARCHAR(26)
        , @LowerCase VARCHAR(26)
		  , @Numbers VARCHAR(10)
		  , @Special VARCHAR(13)
		  , @Temp VARCHAR(8000)
		  , @Password VARCHAR(8000)
		  , @i SMALLINT
		  , @c VARCHAR(1)
		  , @v TINYINT 
-- Set the default items in each group of characters
SELECT @UpperCase = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
    ,    @LowerCase = 'abcdefghijklmnopqrstuvwxyz'
	 ,    @Numbers = '0123456789'
	 ,    @Special = '!@#$%&*()_+-='
	 ,    @Temp = ''
	 ,    @Password = '' 
-- Enforce some limits on the length of the password
IF @UpperCaseItems > 20
  SET @UpperCaseItems = 20 
IF @LowerCaseItems > 20
  SET @LowerCaseItems = 20 
IF @NumberItems > 20    
  SET @NumberItems = 20 
IF @SpecialItems > 20    
  SET @SpecialItems = 20 

-- Get the Upper Case Items
SET @i = ABS(@UpperCaseItems) 
WHILE @i > 0 AND LEN(@UpperCase) > 0
  SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@UpperCase) + 1
  ,    @c = SUBSTRING(@UpperCase, @v, 1)
  ,    @UpperCase = CASE 
       WHEN @UpperCaseItems < 0 
		    THEN STUFF(@UpperCase, @v, 1, '') 
		 ELSE @UpperCase 
  END
  ,    @Temp = @Temp + @c
  ,    @i = @i - 1 
-- Get the Lower Case Items
SET @i = ABS(@LowerCaseItems) 
WHILE @i > 0 AND LEN(@LowerCase) > 0    
  SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@LowerCase) + 1
  ,    @c = SUBSTRING(@LowerCase, @v, 1)
  ,    @LowerCase = CASE 
       WHEN @LowerCaseItems < 0 
		   THEN STUFF(@LowerCase, @v, 1, '') 
		 ELSE @LowerCase 
  END
  ,    @Temp = @Temp + @c
  ,    @i = @i - 1 

-- Get the Number Items
SET @i = ABS(@NumberItems) 
WHILE @i > 0 AND LEN(@Numbers) > 0    
  SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Numbers) + 1
  ,    @c = SUBSTRING(@Numbers, @v, 1)
  ,    @Numbers = CASE 
       WHEN @NumberItems < 0 
		   THEN STUFF(@Numbers, @v, 1, '') 
		 ELSE @Numbers 
  END
  ,    @Temp = @Temp + @c
  ,    @i = @i - 1 
  
-- Get the Special Items
SET @i = ABS(@SpecialItems) 
WHILE @i > 0 AND LEN(@Special) > 0    
  SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Special) + 1
  ,    @c = SUBSTRING(@Special, @v, 1)
  ,    @Special = CASE 
       WHEN @SpecialItems < 0 
		   THEN STUFF(@Special, @v, 1, '') 
	    ELSE @Special 
  END
  ,    @Temp = @Temp + @c
  ,    @i = @i - 1 
  
-- Scramble the order of the selected items
WHILE LEN(@Temp) > 0    
  SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Temp) + 1
  ,    @Password = @Password + SUBSTRING(@Temp, @v, 1)
  ,    @Temp = STUFF(@Temp, @v, 1, '') 

SELECT @Password



/*

--
exec uspCreatePassword 2,2,2,2

----------
MT&4g(5j


exec uspCreatePassword 2,2,4,0

------------
38Io84Vw

*/

[yuna@liu]

create table test_a 
(
 pwd char(6)
)
--
declare @i int 
declare @str char(6)
declare @d int 
set @i=0
set @d=0
while @i<100000
begin 
 select @str=left (newid(),6)
  select @d=count(1) from test_a where pwd=@s
  if @d=0
  begin 
  insert into test_a (pwd)values (@str)
  set @i=@i+1
  end
end

我试了一下 ---一个半小时--大概生成了20万的随机密码
⊙﹏⊙b汗！！！！！！！！！！

[SQL77]

SELECT LEFT(ABS(CHECKSUM(NEWID())),6)

全数字

[wujinyuan]

1.由多少字符组成的字符串
2.得一个机数
3.提对应的出字符串中的字符，提6次
4.判断是否重复
5.不重复保存

[wujinyuan]

[Quote=引用 2 楼 wujinyuan 的回复:]

1.由多少字符组成的字符串
2.得一个机数
3.提对应的出字符串中的字符，提6次
4.判断是否重复
5.不重复保存
[/Quote]用sql没写过。用其他语言写过,我想流程是一样.

[yuna@liu]

全数字 应该不行吧--6位全数字的， 无法生成100万不重复的密码

[水族杰纶]

select RIGHT(REPLACE(NEWID(), '-', ''),6)

如果判断是否重复
可以用查表法

[永生天地]

--1
declare @s varchar(6),@i numeric(10,0)
set @i=1
while 1=1
begin
	set @s=left (newid(),6)
	if not exists(select 1 from tb where code=@s)
	begin
		insert tb (code) values(@s) 
		set @i=@i+1
		if @i>1000000
			break
	end 
	else
		continue
end


--2
declare @s varchar(6),@i numeric(10,0)
set @i=1
while 1=1
begin
	insert tb (code) values(left (newid(),6)) 
	if @@error=0
	begin
		set @i=@i+1
		if @i>1000000
			break
	end 
	else
		continue
end

正在试2，速度不快

[leigenxiang]

--生成26个字母+10个数字的6位随机码 @index表示总共要生成多少个
--璩雷2011-03-15
declare @s char(36)
set @s='QWERTYUIOPASDFGHJKLZXCVBNM0123456789'

declare @index int
set @index = 10000

declare @tmpPasspord char(6)

while(@index>0)
begin
set @tmpPasspord = SUBSTRING(@s,convert(int, RAND()*36+1),1)
+SUBSTRING(@s,convert(int, RAND()*36+1),1)
+SUBSTRING(@s,convert(int, RAND()*36+1),1)
+SUBSTRING(@s,convert(int, RAND()*36+1),1)
+SUBSTRING(@s,convert(int, RAND()*36+1),1)
+SUBSTRING(@s,convert(int, RAND()*36+1),1)
if not exists (select 1 from passpord where Passpord=@tmpPasspord)
begin
insert into passpord (passpord) values (@tmpPasspord)
set @index=@index-1
end
end

--select * from Passpord order by id
--truncate table Passpord