关于SQL生成随机数的问题

Question

--取得随机数的视图
CREATE VIEW v_RAND
AS
SELECT re=STUFF(RAND(),1,2,'')
GO--生成随机编号的函数
CREATE FUNCTION f_RANDBH(@BHLen int)
RETURNS varchar(50)
AS
BEGIN
DECLARE @r varchar(50)
IF NOT(ISNULL(@BHLen,0) BETWEEN 1 AND 50)
SET @BHLen=10 SELECT @r=CHAR(65
+(SUBSTRING(re,1,1)
+SUBSTRING(re,2,1)
+SUBSTRING(re,3,1))%26)
+CHAR(65
+(SUBSTRING(re,4,1)
+SUBSTRING(re,5,1)
+SUBSTRING(re,6,1))%26)
FROM v_RAND
WHILE LEN(@r)<@BHLen
SELECT @r=@r+CHAR(65
+(SUBSTRING(re,1,1)
+SUBSTRING(re,2,1)
+SUBSTRING(re,3,1))%26)
+CHAR(65
+(SUBSTRING(re,4,1)
+SUBSTRING(re,5,1)
+SUBSTRING(re,6,1))%26)
FROM v_RAND
RETURN(LEFT(@r,@BHLen))
END
GO--调用
SELECT dbo.f_RANDBH(6),dbo.f_RANDBH(8)
--结果: UJXIJD  PAPGTQUX

Answer 1

CREATE TABLE tb(id int identity,name NVARCHAR(10))
INSERT tb VALUES('小梁')
INSERT tb VALUES('小梁')
INSERT tb VALUES('liangCK')
INSERT tb VALUES('csdn')
GO
--取得随机数的视图
CREATE VIEW v_RAND
AS
SELECT re=STUFF(RAND(),1,2,'')
GO--生成随机编号的函数
CREATE FUNCTION dbo.f_RANDBH(@BHLen int)
RETURNS varchar(50)
AS
BEGIN
DECLARE @r varchar(50)
IF NOT(ISNULL(@BHLen,0) BETWEEN 1 AND 50)
SET @BHLen=10 SELECT @r=CHAR(65
+(SUBSTRING(re,1,1)
+SUBSTRING(re,2,1)
+SUBSTRING(re,3,1))%26)
+CHAR(65
+(SUBSTRING(re,4,1)
+SUBSTRING(re,5,1)
+SUBSTRING(re,6,1))%26)
FROM v_RAND
WHILE LEN(@r)<@BHLen
SELECT @r=@r+CHAR(65
+(SUBSTRING(re,1,1)
+SUBSTRING(re,2,1)
+SUBSTRING(re,3,1))%26)
+CHAR(65
+(SUBSTRING(re,4,1)
+SUBSTRING(re,5,1)
+SUBSTRING(re,6,1))%26)
FROM v_RAND
RETURN(LEFT(@r,@BHLen))
END
GO--更新
UPDATE a 
  SET name=dbo.f_RANDBH(10)
FROM tb AS a
 JOIN 
 (
    SELECT name 
    FROM tb 
    GROUP BY name
    HAVING COUNT(*)>1
 ) AS b
ON a.name=b.name--查询
SELECT * FROM tbGO
--删除测试
DROP TABLE tb
DROP VIEW v_RAND
DROP FUNCTION dbo.f_RANDBH/*
id          name
----------- ----------
1           PIUCJJBIQD
2           UPDCXBOIPZ
3           liangCK
4           csdn(4 行受影响)*/

Answer 2

--
UPDATE TableName
SET    Name= LEFT(REPLACE(CAST(NEWID() AS VARCHAR(40)),'-',''),10)
FROM   TableName as A
       INNER JOIN 
       (
       SELECT name FROM TableName GROUP BY name HAVING COUNT(name)>1
       ) B
       ON a.Name=b.Name